問題描述
我正在學習如何避免 SQL 注入，但我有點困惑.
使用bind_param時，我不明白目的.在手冊頁上，我找到了這個例子:
$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);$code = 'DEU';$language = '巴伐利亞';$official = "F";$% = 11.2;現在，假設這 4 個變量是用戶輸入的，我不明白這如何防止 SQL 注入.據我了解，他們仍然可以在那里輸入任何他們想要的東西.
我也找不到對 'sssd' 的解釋.它有什么作用?這就是它更安全的原因嗎?
最后一個問題:我在另一個問題上讀到 mysqli_real_escape_string 已被棄用，但手冊中沒有這么說.它是如何被棄用的?是不是因為某種原因不能轉義特殊字符了?
注意:這個問題解釋了 bind_param 的作用，但我仍然不明白為什么它更安全或更受保護.Bind_param 解釋
現在，假設這 4 個變量是用戶輸入的，我不明白這如何防止 SQL 注入.據我了解，他們仍然可以在那里輸入他們想要的任何東西.
主要原則是使用準備好的語句，該語句旨在將安全查詢發送到數據庫服務器，這可以通過轉義不屬于實際查詢的用戶輸入來完成，并且還可以在沒有任何(where 子句)的情況下檢查查詢在使用任何參數之前檢查查詢的有效性.
來自這個問題:PDO 向 MySQL 發送原始查詢，而 Mysqli 發送準備好的查詢，兩者產生相同的結果
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {$stmt->bind_param("i", $user);$user = "''1''";服務器日志:
130802 23:39:39 175 Connect ****@localhost on testdb175 準備 SELECT * FROM users WHERE username =?175 執行SELECT * FROM users WHERE username =0第175話通過使用prepared statement，db server會在不帶任何參數的情況下檢查查詢，在這個階段，可以在綁定任何參數之前檢測錯誤，然后，如果查詢有效，參數也會被發送到服務器進行最終確定查詢.
來自 PHP 手冊 http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:
<塊引用>轉義和 SQL 注入
綁定的變量將被服務器自動轉義.這服務器在適當的位置插入他們的轉義值到執行前的語句模板.必須提供提示給服務器為綁定變量的類型，創建一個合適的轉換.有關更多信息，請參閱 mysqli_stmt_bind_param() 函數信息.
...
<塊引用>我也找不到對那里的sssd"的解釋.它有什么作用做?這就是它更安全的原因嗎?
答案在這里:http://php.net/manual/en/mysqli-stmt.bind-param.php
i對應變量的類型為整數d對應變量的類型為 double秒對應變量的類型為字符串乙對應的變量是一個 blob，將在數據包中發送<塊引用>
最后一個問題:我在另一個問題上讀到mysqli_real_escape_string 已棄用，但在手冊.它是如何被棄用的?不能轉義特殊字符嗎出于某種原因不再使用?
可以給個參考嗎?我想你誤解了 (mysql_real_escape_string())
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
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())
這篇關于bind_param 有什么作用?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！