-1

I was looking at my buddy's site earlier, and I came across the most strange SQL error I have ever encountered. The PHP file is add_cart_new.php?id= which will add a product to the shopper's cart.

If you have add_cart_new.php?id=184' you'll get an error that says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

insert into cart set session_id='1ibltcqbpziwstrilad0e9p61bztnu',product=184'

The part that got me was the ''' at line 1.

I tried doing some other things like entering:

add_cart_new.php?id=add_cart_new.php&return=d4.php

which would be considered to be a local file inclusion vulnerability, I'm guessing.

Anyway, that led to this error:

Unknown column 'add_cart_new.php' in 'field list'

insert into cart set session_id='1ibltcqbpziwstrilad0e9p61bztnu',product=add_cart_new.php

I've researched this lots, but is there any way someone could actually exploit this to get sensitive information? I've looked at lots of guides, and have tried tons of things. None of them have worked.

Community
  • 1
  • 1
RydallCooper
  • 19,094
  • 1
  • 11
  • 17
  • It's not weird, single-quotes screw up MySQL. Your buddy is not properly escaping and is clearly not using prepared statements when assembling queries. – MonkeyZeus Mar 15 '14 at 17:49
  • I'm not really sure what the question is here. Surely there's a problem: your code is vulnerable to SQL injection, and a solution: use prepared statements. – Oliver Charlesworth Mar 15 '14 at 17:49
  • I am not sure if I got your question right. Are you looking for the guides that will help you actually exploit this? – Your Common Sense Mar 15 '14 at 17:57
  • You've correctly recognized that this is SQL injection, and if you know what SQL injection is, you're probably aware that the reason it's bad is that it's an exploitable security flaw. So it's not clear why you're asking whether this exploitable. The answer is yes. :-) – Wyzard Mar 15 '14 at 17:58
  • I'm not understanding how it is exploitable. What value in the parameters would even make it exploitable? I've never encountered such a weird vulnerability like this before. I find it very interesting. – RydallCooper Mar 15 '14 at 18:00
  • @RydallCooper Trivially: `insert into cart set session_id='1ibltcqbpziwstrilad0e9p61bztnu',product=>>184,special_price=0<<` (where >>this was injected<<). Of course there are many more considerations, but the point is there *is* an SQL injection vulnerability *because* the query shape can be changed. Enough of these tiny holes, with enough strange interactions and violated assumptions and you are going to get burned. Just write correct code. – user2864740 Mar 15 '14 at 18:01
  • @user2864740, in fact, PDO allows multi-query *by default*. – Bill Karwin Mar 15 '14 at 18:03
  • @BillKarwin I did not realize that was the case. Removed the other comment. That opens up the classical can. – user2864740 Mar 15 '14 at 18:04
  • 2
    Showing the proper way to exploit SQL code is frowned upon because it opens the educational gates for people that want to maliciously use that knowledge. Consider this error as a slap on the butt to fix the code so that regular users don't think your site is broken. Best practices should be followed at all times because believe it or not, there are people way smarter than anyone on this site which would decimate your buddy's website in seconds. – MonkeyZeus Mar 15 '14 at 18:04
  • 1
    @MonkeyZeus Discussing how SQL Injection works is hardly to be frowned upon. Not letting programmers know *what* SQL injection is and *how* it works (other than "it's dangerous") is akin to holding up a towel - if you can't see it, it can't see you. (However, trivial exploitation examples are readily available online via [SQL injection](http://en.wikipedia.org/wiki/SQL_injection) - is Wikipedia to be "frowned upon"? - etc.) – user2864740 Mar 15 '14 at 18:05
  • Why do you think setting `id=add_cart_new.php` implies a local file inclusion? – Gumbo Mar 15 '14 at 18:07
  • 1
    @user2864740 your statement is equivalent to saying that everyone using a bulletproof should also learn how to make bullets. – MonkeyZeus Mar 15 '14 at 18:09
  • 1
    @MonkeyZeus No, not really (although I see no problem with making bullets). Rather, I think that, if you say to someone, "put this vest on, it'll keep you safe", you should be able to back this up. This should include an explanation of how the vest works, what it protects against (what it doesn't protect against), and perhaps even a simulated field experience perhaps. – user2864740 Mar 15 '14 at 18:11
  • @user2864740 there are two questions actually. How to inject and how to exploit. Particular exploit has absolutely nothing to do neither with "how injection works" nor with "how to make protection". This query is proven to be injectable. That's enough. One have to **seal the leak**, not learn the ways it can be exploited. – Your Common Sense Mar 15 '14 at 18:13
  • 1
    @MonkeyZeus Not understanding SQL injections results in using `mysql_real_escape_string` on data not intended to be used in string literals like as integers, identifiers, language keywords, etc. – Gumbo Mar 15 '14 at 18:17
  • 1
    @Gumbo that's an entirely separate issue. `mysql_real_escape_string` is the equivalent mindset of *welp, bullets are dangerous but we cannot figure out the strap-system nor afford kevlar vests (or w/e is the best) so let's put on these drift-wood vests and hope for the best* – MonkeyZeus Mar 15 '14 at 18:21
  • @MonkeyZeus No, it’s not. People get told to use `mysql_real_escape_string` for user supplied data. But they almost never get told that `mysql_real_escape_string`’s return value is only supposed to be used on values that are to be used in MySQL string literals and anything else. So a metaphor would rather be: A Kevlar vest does only protect your chest but won’t protect you getting shot in the foot because it’s designed to only protect your chest. – Gumbo Mar 15 '14 at 18:28
  • 1
    @Gumbo what is all this talk about? The OP is CLEARLY asking NOT how to protect but how to exploit. – Your Common Sense Mar 15 '14 at 18:34
  • @YourCommonSense we don't even know anymore... – MonkeyZeus Mar 15 '14 at 18:36

2 Answers2

3

This is not weird at all, this is a very common case of vulnerable code.

The part that got me was the ''' at line 1.

This means that MySQL is reporting that it didn't expect to see a quote character at the place where it occurred, at the end of the SQL statement:

insert into cart set session_id='1ibltcqbpziwstrilad0e9p61bztnu',product=184'

That last quote at the end is a syntax error according to SQL. Normally MySQL syntax error messages include more text following the point where it saw something it didn't expect, but since in this case it was the end of the statement, it had only a single quote character to report in the error.

Unknown column 'add_cart_new.php' in 'field list'

insert into cart set session_id='1ibltcqbpziwstrilad0e9p61bztnu',product=add_cart_new.php

It would be legitimate syntax in an INSERT statement to set a field to an expression, which allows for the tablename.columnname syntax. Admittedly, it doesn't make much sense in an INSERT, but it's legal expression syntax.

That's the only plausible way the SQL parser can interpret add_cart_new.php -- as an identifier where add_cart_new is a table name, and php is a column name.

is there any way someone could actually exploit this to get sensitive information?

Yes, lots. An attacker could for example use a subquery as the expression, and then load data into their cart from anywhere else in the database the web app has privileges to read.

Or they could use an expression containing a function that accesses resources outside the database, like LOAD_FILE().

Or they could use a literal comma character, and let that INSERT statement set other fields, which could change the price paid for a product, or to whom it will be billed.

Or they could use a literal semicolon character, and execute another whole statement after the INSERT.

SQL injection is insidious and the perpetrators are motivated to be very clever. Just follow correct coding practices and use query parameters instead of copying $_GET variables directly into SQL strings.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

SQL Injection is an attack which allows the shape of a query to be changed.

Starting with the following hypothetical query without placeholders and, as indicated by the error, without other escaping:

insert into cart set session_id=$session,product=$v

Then this represents an SQL Injection vulnerability, as it can be noted that id=184' affects $v. In the initial value proposed it merely resulted in [benign] invalid SQL syntax, but ..

A classic example for $v is something like:

0; DELETE FROM users;

This would be replaced as insert into ..,product=0; DELETE FROM users which is clearly bad. Now, some PHP providers don't allow multi-statements by default, which mitigate this scenaio, but .. imagine if $v was merely

184,special_price=0

Now the result query would be insert into ..,product=0,special_price=0 and, making some other hypothetical assumptions about how such a cart might work, we've just awarded a free product!.

The problem with SQL Injection - whether it is a single statement, or multiple statements - is that it opens up the door for exploitation. The current situation might not be exploitable, but each open door/window increases the chance of being robbed.

(This is why I, and many others, use parameterized queries - which eliminate SQL injection - for all data.)

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • Yes, parameters are definitely recommended, but they don't solve SQL injection for all queries. What if I want to allow the user to specify which column to sort by? And which sort direction? – Bill Karwin Mar 15 '14 at 19:08
  • @BillKarwin Well, trivially one uses a whitelist or framework which provides similar safety in it's dynamic query generation. That being said, point taken - changed "queries" to "data". (Edge cases where parameterized queries just "won't work" for certain data/situations should be entertained in other questions.) – user2864740 Mar 15 '14 at 19:13
  • Yes, one parameter = one scalar value in an expression. Everything else needs other techniques to ensure it's safe to interpolate into the SQL statement before prepare time, and whitelisting is a good one. – Bill Karwin Mar 15 '14 at 19:52