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())