問題描述

我讀過這個:

將幫助您避免注射.因為轉(zhuǎn)義只是一種字符串格式化工具，無論如何都不是注入防止器.去搞清楚.但是，轉(zhuǎn)義與準(zhǔn)備好的語句有一些共同點(diǎn):他們都不能保證你注射，如果您僅將它用于臭名昭著的用戶輸入"，而不是構(gòu)建任何查詢的嚴(yán)格規(guī)則，盡管有數(shù)據(jù)源.以防您需要插入的不是數(shù)據(jù)而是標(biāo)識符或關(guān)鍵字.

在以下帖子中:帶有 sql 轉(zhuǎn)義的動態(tài) mysql 查詢是否與準(zhǔn)備好的語句一樣安全?

所以我的問題是使用:

$Var = "用戶輸入數(shù)據(jù)可能存在 SQL 注入";$mysqli->real_escape_string($Var);

不提供針對 SQL 注入的保護(hù)?

我想使用 $mysqli->query(); 所以我可以使用 fetch_array(MYSQLI_ASSOC); 因為坦率地說，我不知道如何使用 prepared 語句后以數(shù)組的形式獲取結(jié)果.

所以如果我的數(shù)據(jù)庫連接中有這個:

$STD = new mysqli('localhost', 'root', 'xx', 'xx');$STD->set_charset('utf8');如果 ($STD->connect_error) {die("標(biāo)準(zhǔn)訪問權(quán)限已被撤銷.請聯(lián)系管理員");}elseif (!$STD){die(連接到數(shù)據(jù)庫的其他問題，請聯(lián)系管理")；}

如real_escape_string的手冊所述

http://php.net/manual/en/mysqli.real-escape-string.php

以上列表:

注意安全性:默認(rèn)字符集必須在服務(wù)器級別設(shè)置字符集，或者使用 API 函數(shù) mysqli_set_charset() 設(shè)置字符集才能影響 mysqli_real_escape_string().有關(guān)詳細(xì)信息，請參閱有關(guān)字符集的概念部分.

鏈接到:http://php.net/manual/en/mysqli.set-charset.php

我的總體問題可以分為三個選項，第一個是要求 fetch_array() 等價于 prepared 語句，這將提供完整的 SQL 注入預(yù)防，因為準(zhǔn)備好的語句以原始形式發(fā)送數(shù)據(jù).

這種格式的第一個問題如下:

我使用查詢作為:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");$GetCompletedArray = $GetCompletedQuery->fetch_array(MYSQLI_ASSOC);

返回:

數(shù)組([狀態(tài)] => 1)

但是使用準(zhǔn)備好的語句:

$GetCompletedQuery = $STD->prepare("SELECT Status FROM UserCompletion WHERE `UserID`=?");$GetCompletedQuery->bind_param('i', $_SESSION['UID']);$GetCompletedQuery->execute();$GetCompletedArray = $GetCompletedQuery->fetch_row;打印_r($GetCompletedArray);

返回:

致命錯誤:在第 17 行的/var/www/New/API/Constants.php 中的非對象上調(diào)用成員函數(shù) fetch_row()

當(dāng)我嘗試 fetch_array() 時出現(xiàn)同樣的情況，我知道它不能與準(zhǔn)備好的語句一起使用.

那么使用準(zhǔn)備好的語句有什么選擇?

第二個問題

如果我使用我的常用查詢:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");

這使我能夠使用 fetch_array(); 是否從 SQL 注入中正確保護(hù)了數(shù)據(jù)?

第三個問題:

我是否應(yīng)該逃避/保護(hù) $_SESSION['UID']; 的 SQL 注入，因為這是在以下莊園中分配的:

$InnerJoinQuery = $STD->query("SELECT Users.ID、Users.Username、Users.Password、UserInformation.LastName、UserInformation.Firstname、UserInformation.DOB來自用戶INNER JOIN 用戶信息ON Users.ID = UserInformation.UserID WHERE Users.Username = '".$_SESSION['real_name']."'");$InnerJoinArray = $InnerJoinQuery->fetch_array(MYSQLI_ASSOC);$_SESSION['UID'] = $InnerJoinArray['ID'];$_SESSION['密碼'] = $InnerJoinArray['密碼'];$_SESSION['Firstname'] = $InnerJoinArray['Firstname'];$_SESSION['LastName'] = $InnerJoinArray['LastName'];$_SESSION['DOB'] = $InnerJoinArray['DOB'];

這段代碼解釋了:

用戶使用用戶名 & 登錄密碼，文件根據(jù)$_SESSION['real_name'];從數(shù)據(jù)庫中獲取信息并將結(jié)果添加到 $_SESSION 數(shù)組中，將每個添加到不同的鍵中.

這個塊的問題是當(dāng) $_SESSION['UID']; 通過基于 $_SESSION[' 的數(shù)據(jù)庫分配時，我是否應(yīng)該逃避/保護(hù) SQL 注入real_name'];

感謝您花時間閱讀這一大塊內(nèi)容.

1. http://php.net/manual/en/mysqli-stmt.get-result.php
2. 是的，但這是非常糟糕的做法:
   • 它會在這種情況下幫助您，但僅在這種情況下會以其他方式欺騙
   • 手動轉(zhuǎn)義太傻了，最好讓司機(jī)幫你做
3. 是的，因為沒有 SQL 注入之類的東西，只有格式不正確

<塊引用>

使用 $mysqli->real_escape_string($Var); 是否不能提供針對 SQL 注入的保護(hù)?

我沒有改變主意:當(dāng)然沒有.
僅當(dāng)您將結(jié)果值括在引號中(并使用 mysqli_set_charset() 將正確編碼設(shè)置為嚴(yán)格時，它才會這樣做).

看，SQL 注入不是必不可少的東西，它自己存在，但它只是一個結(jié)果.查詢格式不正確的后果.
創(chuàng)建查詢時，您必須正確格式化查詢的每個部分.不是因為什么注射"，而是為了它.當(dāng)您要在查詢中插入字符串時，必須將其放入引號中，否則會出現(xiàn)語法錯誤.當(dāng)您要在查詢中插入一個字符串時，您必須轉(zhuǎn)義這些用于分隔該字符串的引號，否則您將收到語法錯誤.等等.您應(yīng)該關(guān)注正確的格式，而不是有關(guān)注射的可怕故事.而且只要您根據(jù)類型正確格式化每個動態(tài)查詢部分 - 不可能進(jìn)行注入

因此，變量的來源或其價值永遠(yuǎn)不應(yīng)成為您的關(guān)注點(diǎn).但只有它在查詢中的位置:

• 字符串必須用引號括起來并轉(zhuǎn)義這些引號.
• 數(shù)字必須轉(zhuǎn)換為它的類型.
• 標(biāo)識符必須用反引號括起來，并將這些反引號加倍

當(dāng)查詢的 static 部分在腳本中硬編碼時，我們不會使用如此嚴(yán)格的標(biāo)準(zhǔn) - 例如，我們不會將每個標(biāo)識符都包含在反引號中.
但是當(dāng)涉及查詢的動態(tài)部分時，應(yīng)用格式規(guī)則應(yīng)該是嚴(yán)格的規(guī)則，因為我們無法確定變量內(nèi)容.

順便說一下，還有另一種格式化字符串和數(shù)字的方法 - 準(zhǔn)備好的語句.它不像它應(yīng)該的那樣方便，但是因為它使用占位符來表示查詢中的數(shù)據(jù)，所以建議使用愚蠢的手動格式.

I have read this:

will help you NOT against injection. Beause escaping is just a string formatting facility, not injection preventer by any means. Go figure. However, escaping have something in common with prepared statements: Them both doesn't guarantee you from injection if you are using it only against notorious "user input", not as a strict rule for the building ANY query, despite of data source. in case you need to insert not data but identifier or a keyword.

On the following Post: Are dynamic mysql queries with sql escaping just as secure as prepared statements?

So my question is that using:

$Var = "UserInput Data Possible SQL Injection";
$mysqli->real_escape_string($Var);

does not provide protection against SQL Injection?

I want to use $mysqli->query(); so I can use fetch_array(MYSQLI_ASSOC); Because to be frank, I have no idea how to fetch the results as an array after using a prepared statement.

So If I have this in my Database Connection:

$STD = new mysqli('localhost', 'root', 'xx', 'xx');
$STD->set_charset('utf8');

if ($STD->connect_error) {
    die("Standard Access Has Been Revoked. Please Contact Administration"); 
}elseif (!$STD){
die ("Other problem With Connecting To Database, Please Contact Administration");
}

as stated in the manual for real_escape_string

http://php.net/manual/en/mysqli.real-escape-string.php

The above lists:

Caution Security: the default character set The character set must be set either at the server level, or with the API function mysqli_set_charset() for it to affect mysqli_real_escape_string(). See the concepts section on character sets for more information.

Which links to: http://php.net/manual/en/mysqli.set-charset.php

My overall question can split into three options, the first would be asking for a fetch_array() equlivant for prepared statements, which will provide full SQL injection prevention due to prepared statements sending data as raw.

The first question in this format follows:

I'm using a Query as:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");
$GetCompletedArray = $GetCompletedQuery->fetch_array(MYSQLI_ASSOC);

Which returns:

Array ( [Status] => 1 )

But using prepared statements:

$GetCompletedQuery = $STD->prepare("SELECT Status FROM UserCompletion WHERE `UserID`=?");
$GetCompletedQuery->bind_param('i', $_SESSION['UID']);
$GetCompletedQuery->execute();

$GetCompletedArray = $GetCompletedQuery->fetch_row;

print_r($GetCompletedArray);

Which returns:

Fatal error: Call to a member function fetch_row() on a non-object in /var/www/New/API/Constants.php on line 17

The same appears when I try fetch_array() which I know cannot be used with prepared statements.

So what would be the option for using prepared statements?

Second Question

If I use My Usual Query as:

$GetCompletedQuery = $STD->query("SELECT Status FROM UserCompletion WHERE `UserID`=' ". $STD->real_escape_string($_SESSION['UID']) ."'");

which enabled me to use fetch_array(); is data properly secured from SQL injection?

Third Question:

Should I be escaping/protecting from SQL injection for a $_SESSION['UID']; as this is assigned in the following manor:

$InnerJoinQuery = $STD->query("
        SELECT Users.ID, Users.Username, Users.Password, UserInformation.LastName, UserInformation.Firstname, UserInformation.DOB
        FROM Users
        INNER JOIN UserInformation
        ON Users.ID = UserInformation.UserID WHERE Users.Username = '".$_SESSION['real_name']."'");
        $InnerJoinArray = $InnerJoinQuery->fetch_array(MYSQLI_ASSOC);

    $_SESSION['UID'] = $InnerJoinArray['ID'];
    $_SESSION['Password'] = $InnerJoinArray['Password'];
    $_SESSION['Firstname'] = $InnerJoinArray['Firstname'];
    $_SESSION['LastName'] = $InnerJoinArray['LastName'];
    $_SESSION['DOB'] = $InnerJoinArray['DOB'];

This snippet explained:

User Logs in with username & password, the file gets information from the database based on $_SESSION['real_name']; and adds to the $_SESSION array with the results, adding each into a different key.

The question for this chunk is should I even be escaping/protecting from SQL injection when the $_SESSION['UID']; is assigned through the database based on $_SESSION['real_name'];

Thankyou for your time for reading over this massive chunk.

1. http://php.net/manual/en/mysqli-stmt.get-result.php
2. Yes, but it is very bad practice:
   • it will help you in this case but only in this case and deceive with anything else
   • manual escaping is just silly, better let driver to do it for you
3. YES, because there is no such thing like SQL injection but improper formatting ONLY

is that using $mysqli->real_escape_string($Var); does not provide protection against SQL Injection?

I didn't change my mind: sure, it doesn't.
It will do only if you enclose the resulting value in quotes (and set proper encoding using mysqli_set_charset() to be strict).

Look, SQL injection not something essential, existing on it's own, but it's rather mere a consequence. A consequence of improperly formatted query.
When creating a query, you have to properly format every part of it. Not because of whatever "injection" but for the sake of it. When you're going to insert a string into query, you HAVE to put it into quotes, or you will get a syntax error. When you're going to insert a string into query, you HAVE to escape these quotes were used to delimit this string, or you will get a syntax error. And so on. It is proper formatting that should be your concern, not scaring tales about injection. And as long as you have every dynamic query part properly formatted according to it's type - no injection ever could be possible

So, the source of variable or it's value should never be your concern. But only it's place in the query:

• strings have to be enclosed in quotes and have these quotes escaped.
• numbers have to be cast to it's type.
• identifiers have to be enclosed in backticks and have these backticks doubled

When it's going for the static part of the query, hardcoded in the script, we don't use such strict standards - say, we're not enclosing every identifier in backticks.
But when it's going for the dynamical part of the query, applying formatting rules should be strict rule, as we cannot know variable content for sure.

By the way, there is another way to format your strings and numbers - prepared statements. It is not as convenient as it should be, but because it is using placeholders to represent your data in the query, it it recommended to use over silly manual formatting.

這篇關(guān)于使用 MySQLI 正確轉(zhuǎn)義 |查詢準(zhǔn)備好的語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！