Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

As the title states, why does MySQLi use question marks instead of sprintf() formatting? For example, why is it

$stmt = $mysqli->prepare("SELECT * FROM table WHERE column1 = ?");
$stmt->bind_param('s', $var);

instead of

$stmt = $mysqli->prepare("SELECT * FROM table WHERE column1 = %s", $var);

Were there some complications with using the sprintf formatting, or were there security risks associated with it? Additionally, if you needed to have a question mark in your SQL statement, is there a way to escape it (in sprintf(), you would just do %% instead of %)?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

There is no particular standard for what parameterised queries look like, so it could just as well be any syntax. I'm not sure what DB layer you were previously using that preferred %s (but if it's the Python MySQLdb then the reason for that is that it's using its own non-native parameterisation function which actually uses the string format operator to drop the parameters into the query).

PHP's mysqli_prepare is implemented using the libmysql-native parameterisation C function mysql_stmt_prepare and that function decided to use question marks.

if you needed to have a question mark in your SQL statement, is there a way to escape it

You don't need to escape it: apart from representing a parameter, the only place you can use a question mark in SQL is in a string literal. ? doesn't have special powers in a string literal, so:

SELECT '?' FROM x WHERE x.id=?

selects a literal question mark where the id matches the parameter.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.