3

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 %)?

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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