問題描述
我正在學(xué)習(xí)如何避免 SQL 注入，但我有點(diǎn)困惑.
使用bind_param時(shí)，我不明白目的.在手冊頁上，我找到了這個(gè)例子:
$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;現(xiàn)在，假設(shè)這 4 個(gè)變量是用戶輸入的，我不明白這如何防止 SQL 注入.據(jù)我了解，他們?nèi)匀豢梢栽谀抢镙斎肴魏嗡麄兿胍臇|西.
我也找不到對 'sssd' 的解釋.它有什么作用?這就是它更安全的原因嗎?
最后一個(gè)問題:我在另一個(gè)問題上讀到 mysqli_real_escape_string 已被棄用，但手冊中沒有這么說.它是如何被棄用的?是不是因?yàn)槟撤N原因不能轉(zhuǎn)義特殊字符了?
注意:這個(gè)問題解釋了 bind_param 的作用，但我仍然不明白為什么它更安全或更受保護(hù).Bind_param 解釋
現(xiàn)在，假設(shè)這 4 個(gè)變量是用戶輸入的，我不明白這如何防止 SQL 注入.據(jù)我了解，他們?nèi)匀豢梢栽谀抢镙斎胨麄兿胍娜魏螙|西.
主要原則是使用準(zhǔn)備好的語句，該語句旨在將安全查詢發(fā)送到數(shù)據(jù)庫服務(wù)器，這可以通過轉(zhuǎn)義不屬于實(shí)際查詢的用戶輸入來完成，并且還可以在沒有任何(where 子句)的情況下檢查查詢在使用任何參數(shù)之前檢查查詢的有效性.
來自這個(gè)問題:PDO 向 MySQL 發(fā)送原始查詢，而 Mysqli 發(fā)送準(zhǔn)備好的查詢，兩者產(chǎn)生相同的結(jié)果
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {$stmt->bind_param("i", $user);$user = "''1''";服務(wù)器日志:
130802 23:39:39 175 Connect ****@localhost on testdb175 準(zhǔn)備 SELECT * FROM users WHERE username =?175 執(zhí)行SELECT * FROM users WHERE username =0第175話通過使用prepared statement，db server會在不帶任何參數(shù)的情況下檢查查詢，在這個(gè)階段，可以在綁定任何參數(shù)之前檢測錯(cuò)誤，然后，如果查詢有效，參數(shù)也會被發(fā)送到服務(wù)器進(jìn)行最終確定查詢.
來自 PHP 手冊 http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:
<塊引用>轉(zhuǎn)義和 SQL 注入
綁定的變量將被服務(wù)器自動轉(zhuǎn)義.這服務(wù)器在適當(dāng)?shù)奈恢貌迦胨麄兊霓D(zhuǎn)義值到執(zhí)行前的語句模板.必須提供提示給服務(wù)器為綁定變量的類型，創(chuàng)建一個(gè)合適的轉(zhuǎn)換.有關(guān)更多信息，請參閱 mysqli_stmt_bind_param() 函數(shù)信息.
...
<塊引用>我也找不到對那里的sssd"的解釋.它有什么作用做?這就是它更安全的原因嗎?
答案在這里:http://php.net/manual/en/mysqli-stmt.bind-param.php
i對應(yīng)變量的類型為整數(shù)d對應(yīng)變量的類型為 double秒對應(yīng)變量的類型為字符串乙對應(yīng)的變量是一個(gè) blob，將在數(shù)據(jù)包中發(fā)送<塊引用>
最后一個(gè)問題:我在另一個(gè)問題上讀到mysqli_real_escape_string 已棄用，但在手冊.它是如何被棄用的?不能轉(zhuǎn)義特殊字符嗎出于某種原因不再使用?
可以給個(gè)參考嗎?我想你誤解了 (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())
這篇關(guān)于bind_param 有什么作用?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！