Is this query vulnerable to sql injection?

Question

$myq = sprintf("select user from table where user='%s'", $_POST["user"]);

I would like to know if the above query can be exploited using SQL injection. Is there any advanced SQL injection technique that could break sprintf for this particular query?

Answer 1

I don't think it needs to be particularly advanced... try an input of

' OR 1 = 1 OR user='

In other words, you'll get SQL of:

select user from table where user='' OR 1 = 1 OR user=''

Does that look like a query you really want to execute? (Now consider the possibility of it dropping tables instead, or something similar.)

The bottom line is that you should be using a parameterised query.

Answer 2

when $_POST["user"] would equal "';SHUTDOWN;" - what would happen?

Answer 3

$_POST["user"] = "' or 1=1 or user='"

Answer 4

Yes.

If somebody put in the following as the user in your form:

'; delete * from table

Answer 5

Using sprintf doesn’t give you any more protection than using simple string concatenation. The advantage of sprintf is just having it a little more readable than when to using simple PHP’s string concatenation. But sprintf doesn’t do any more than simple string concatenation when using the %s format:

$str = implode('', range("\x00", "\xFF"));        // string of characters from 0x00 – 0xFF
var_dump(sprintf("'%s'", $str) === "'".$str."'"); // true

You need to use functions that escape the contextual special characters you want to insert your data into (in this case a string declaration in MySQL, supposing you’re using MySQL) like mysql_real_escape_string does:

$myq = sprintf("select user from table where user='%s'", mysql_real_escape_string($_POST["user"]));

Answer 6

Yes, I'd say you have a potential problem there :)

You need to escape: \x00, \n, \r, \, ', " and \x1a. sprintf() does not do that, sprintf() does no modification to strings, it just expands whatever variadic arguments that you give it into the buffer that you provide according to the format that you specify.

If the strings ARE being transformed, its likely due to magic quotes (as Rob noted in Comments), not sprintf(). If that is the case, I highly recommend disabling them.

Answer 7

Ahh here I come with the magic answer! :)
magic quotes do escaping for you!

So, you have to turn magic_quotes_gps ini directive off
and then use mysql_real_escape_string as suggested.

Answer 8

Actually, turn off magic quotes.

In PHP, where it's appropriate, use filters:

$inUser = $_POST['user'];
$outUser = filter_var($inUser, FILTER_SANITIZE_STRING);

Filters strip out HTML tags and escape various characters.

In addition, you can let your database escape it for you:

$inUser = $_POST['user'];
$outUser = mysqli_real_escape_string($conn, $inUser);

This escapes MySQL specific special characters like double quotes, single quotes, etc.

Finally, you should use parameterized queries:

$sql = "SELECT user FROM table WHERE user = ?";
$stmt = $pdo->prepare($sql);
$params = array($outUser);
$stmt->execute($params);

Parameterized queries automatically add the quotes around strings, etc., and have further restrictions that make SQL injections even more difficult.

I use all three, in that order.