What does bind_param accomplish?

Question

I'm learning about avoiding SQL injections and I'm a bit confused.

When using bind_param, I don't understand the purpose. On the manual page, I found this example:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

Now, assuming those 4 variables were user-inputted, I don't understand how this prevents SQL injections. By my understanding, they can still input whatever they want in there.

I also can't find an explanation for the 'sssd' in there. What does it do? Is that what makes it secure-er?

Final question: I read on another question that mysqli_real_escape_string is deprecated, but it doesn't say that in the manual. How is it deprecated? Can it not escape special characters anymore for some reason?

Note: This question explained what bind_param does, but I still don't understand why it is any safer or more protected. Bind_param explanation

Answer 1

Now, assuming those 4 variables were user-inputted, I don't understand how this prevents SQL injections. By my understanding, they can still input whatever they want in there.

The main principle there is using prepared statement which is designed for sending safe query to db server, this can be done by escaping user input which is not part of the real query, and also checking the query without any (where clause) to check the validity of the query before using any parameters.

From this question: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {
$stmt->bind_param("i", $user);
$user = "''1''";

server logs:

  130802 23:39:39   175 Connect   ****@localhost on testdb
    175 Prepare   SELECT * FROM users WHERE username =?
    175 Execute   SELECT * FROM users WHERE username =0
    175 Quit

By Using prepared statement, db server will check the query without any parameter, at this stage, errors can be detected before binding any parameter, then, if the query was valid, parameters also will be send to the server for finalizing the query.

From PHP Manual http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

..

I also can't find an explanation for the 'sssd' in there. What does it do? Is that what makes it secure-er?

The answer is here: http://php.net/manual/en/mysqli-stmt.bind-param.php

i
corresponding variable has type integer

d
corresponding variable has type double

s
corresponding variable has type string

b
corresponding variable is a blob and will be sent in packets

Final question: I read on another question that mysqli_real_escape_string is deprecated, but it doesn't say that in the manual. How is it deprecated? Can it not escape special characters anymore for some reason?

Can you give a reference? I think you misunderstood with (mysql_real_escape_string())

Answer 2

By using prepared statements you are separating SQL queries from user entered data. Instead of input data, you put placeholders ('?' char) in your SQL query. Then you send the query to the DBMS server (e.g.: MySQL) by means of the "mysqli::prepare" method. So the server checks that everything is ok and, if so, it waits for input data. By now it already knows your query. Just it has to wait for input data to bind to the query.

At this point, "bind_param" comes into action, binding placeholders to user entered data. Notice that bind_param only binds data to placeholders leaving unchanged the query. So there is not way to change the original SQL query, because it has already sent to the server by means of the prepare method and because you are sending SQL queries and input data separately so user entered data can't interfere with queries.

ANYWAY...

The actual purpose to use a prepared statement in SQL is to cut the cost of processing queries, NOT to separate data from query. That's how it's being used now, not how it was designed to be used in the first place.

'sssd' stands for "string", "string", "string" and "double". In fact: $code is a string, $language is a string, $official is a string and $percent is a double type.

mysqli_real_escape_string is not deprecated but mysql_real_escape_string is deprecated (the first one is mysqlI, where I stands for "improved").