MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL Injection Risks

with 10 comments

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL hello('\'Harriet\' OR 1 = 1');

It fails because there’s no matching 'Harriet' OR 1 = 1 in the table’s sample_name column. However, it works well when we submit 'Harriet' by herself, without the intended SQL inject phrase “OR 1 = 1“, as you can see:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         2 | Harriet     |
+-----------+-------------+
1 row in set (0.00 sec)

There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END;
$$

The last hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the sample table.

CALL hello('\'Juliette\' OR 1 = 1');

It would display:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         1 | Hank        |
|         2 | Harriet     |
+-----------+-------------+
2 rows in set (0.00 sec)

While there’s no sample_name value of 'Juliette', the “OR 1 = 1” is true. Therefore, the SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.

A solution with the WHERE clause as part of the parameter would look like this:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  pv_where;
END;
$$

The modified call:

CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');

returns all rows from the table.

A solution without the WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the CONCAT function, which also added nothing to the example once I tested it.

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  pv_where;
END;
$$

It’s simply works only when you provide a “1 = 1″ or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

but this SQL injection statement works:

CALL hello('1 = 1');

returns all rows from the table.

This example, when you omit the white space also works with embedded strings or numeric operands and an operator:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHEREpv_where;
END;
$$

It returns all rows with a call like this:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because WHEREpv_where simply becomes a table alias in the query.

In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.

Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.

Written by maclochlainn

April 5th, 2013 at 8:35 pm

10 Responses to 'SQL Injection Risks'

Subscribe to comments with RSS or TrackBack to 'SQL Injection Risks'.

  1. The last version of “hello()” does not compile. Also, I’m pretty sure you meant to prepare a statement there? Saying “FROM sample pv_where;” merely makes for a table name alias.

    Shlomi Noach

    6 Apr 13 at 10:23 am

  2. Shlomi,

    I’m not sure why it doesn’t compile for you because it does for me. The mistake though was the CALL, I’d fat fingered it when I changed it to Juliette, as shown:

    CALL hello('\'Juliette\' OR 1 = 1');

    It should have been the same as my testing script, which was this:

    CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');

    This is what shows (with a bit of white space help) in the data catalog:

    CREATE DEFINER=`student`@`%` PROCEDURE `hello`(IN pv_where VARCHAR(50))
    BEGIN
      SELECT pv_input AS "WHERE Clause";
      SELECT sample_id
      ,      sample_name
      FROM   sample
      pv_where;
    END

    The CALL statement make it act like a table alias and would cause a runtime failure. Is that what you got?

    maclochlainn

    6 Apr 13 at 5:54 pm

  3. Hi,

    You are correct that it compiles, but immediately fails to run:

    mysql> CALL hello(1);  
    ERROR 1054 (42S22): UNKNOWN COLUMN 'pv_input' IN 'field list'

    Where does “pv_input” come from? You probably meant for “pv_where”?

    Also, in the last case, the reason all rows are returned from the table is NOT because you used “… or 1 = 1″, but because the IS NO where condition. That is, the ‘pv_where’ variable does not get interpreted as a “WHERE” clause, ever. It’s just a table alias. To prove:

    (I’ve commented the “pv_input” line for the routine to work, and have injected two rows into the table)

    mysql> call hello('0 and false');  
    +-----------+-------------+
    | sample_id | sample_name |
    +-----------+-------------+
    | a         | x           |
    | b         | y           |
    +-----------+-------------+

    There is no way “0 and false” would evaluate to TRUE, right? The reason we get both rows in the result set is that there is no WHERE condition.

    Shlomi Noach

    6 Apr 13 at 11:04 pm

  4. Thanks, you’re right I fat fingered it twice because I was using pv_input as the parameter in the sample code, and I forgot to remove my diagnostic line. ;-) It’s fixed now.

    Yes, if you exclude the WHERE from the input, it fails too. There are variants on how people implement this, and I’ve provided another without requiring the WHERE keyword as part of the parameter.

    maclochlainn

    7 Apr 13 at 10:43 am

  5. SQL Injection is a one kind of attack where the attacker generates malicious code and send into SQL query to access database or system. If we follow some techniques we can prevent it. Some techniques:

    1. Use stored procedure (SP)
    2. Re-validate data in stored procedures.
    3. Use parameterized query
    4. Use ORM tools (LINQ, NHybernet, LINQ to Entities)
    5. Use regular expression to discard input string

    For more please visit this site.

    Thanks & regard

    World Traveler

    8 Apr 13 at 1:14 am

  6. Hi,

    “A solution without the WHERE clause as part of the parameter would look like this:

    CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
    BEGIN
      SELECT sample_id
      ,      sample_name
      FROM   sample
      WHERE  CONCAT(' ',pv_where);
    END;
    $$

    You should note that the CONCAT function guarantees a white space between the WHERE keyword and parameter for the filtering comparative logic.”

    Uhm…this doesn’t make sense?

    CONCAT(' ',pv_where)

    will produce a string value, so

    WHERE < string-expression >

    will simply always be true, regardless of the contents of the string, unless the string happens to be NULL?

    Roland Bouman

    9 Apr 13 at 6:47 am

  7. Roland, Thanks for the note. I threw that in without much thought and it appears a bit quirky now with the CONCAT function call removed. That’s why I’ve logged a bug. Anyway, the parser did provide the whitespace, which is what I think you meant to say. The CONCAT function doesn’t actually provide any whitespace unless you introduce it manually, as was done in my example.

    For example, a call to the CONCAT function like this:

    SELECT CONCAT('[','\'Harriet\'',']') AS "Example";

    results in this string with no implicitly provided white space:

    +-------------+
    | Example     |
    +-------------+
    | ['Harriet'] |
    +-------------+
    1 row in set (0.00 sec)

    If you didn’t mean that the parser provides the space, then I need a bit more to see what you’re getting at.

    maclochlainn

    9 Apr 13 at 12:48 pm

  8. “Roland, Thanks for the note. I threw that in without much thought and it appears a bit quirky now with the CONCAT function call removed. That’s why I’ve logged a bug. Anyway, the parser did provide the whitespace, which is what I think you meant to say”

    No, that wasn’t what I meant at all. My point is that you can just write

    WHERE < string-expression >

    and hope that will be parsed as if it was a SQL expression. The WHERE keyword expects a boolean expression, so the will be coerced into a boolean expression. IIRC, a string in MySQL will always be TRUE, unless it’s NULL.

    Note that it doesn’t make any difference whether you write

    WHERE CONCAT(...) vs WHERE pv_where, since both CONCAT() and pv_where are string values at runtime. The fact that the string value would make a valid SQL expression if it would be interpreted as such doesn’t change the fact that it is still just a string value.

    If you do want to use the string content as an expression, then your earlier example with PREPARE would be the only way to do this in MySQL. And indeed, it would be prone to SQL injection, unless you somehow parse and validate the expression conveyed in the string value yourself.

    Hope that clears it up.

    Roland Bouman

    9 Apr 13 at 5:00 pm

  9. Rouland, I understand now. Yes, you mean all of the following return all rows in a table because they’re evaluated in the WHERE predicate as true:

    SELECT sample_id, sample_name
    FROM   sample
    WHERE NOT 'x';

    It needs to be NOT to be true because string coercesion results in a 0 integer value and also raises a warning while making the conversion.

    SELECT sample_id, sample_name
    FROM   sample
    WHERE NOT 0;

    and, these would fail or return empty sets because they’re boolean like false values:

    SELECT sample_id, sample_name
    FROM   sample
    WHERE  0;
    SELECT sample_id, sample_name
    FROM   sample
    WHERE 'x';

    The coercesion warning message when the WHERE predicate is followed by a string without evaluation is:

    mysql> show warnings;
    +---------+------+----------------------------------------+
    | Level   | Code | Message                                |
    +---------+------+----------------------------------------+
    | Warning | 1292 | Truncated incorrect INTEGER value: 'x' |
    +---------+------+----------------------------------------+

    I appreciate all the information you shared. Thanks!

    maclochlainn

    9 Apr 13 at 6:40 pm

  10. Excuse me, can i use local variable add parameter cause SQL Injection?

    CREATE PROCEDURE hello (IN x VARCHAR(50))
    BEGIN
    SET @a = CONCAT(x, '1=1');
    SET @sql = CONCAT('select * from sample where name=', @a);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    END;
    call hello('\'abc\' OR ');

    i’m not sure this syntax is correct…

    William

    26 Jul 13 at 9:44 am

Leave a Reply