問題描述

我讀過這個:

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

在以下帖子中:帶有 sql 轉義的動態 mysql 查詢是否與準備好的語句一樣安全?

所以我的問題是使用:

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

不提供針對 SQL 注入的保護?

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

所以如果我的數據庫連接中有這個:

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

如real_escape_string的手冊所述

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

以上列表:

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

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

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

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

我使用查詢作為:

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

返回:

數組([狀態] => 1)

但是使用準備好的語句:

$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 中的非對象上調用成員函數 fetch_row()

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

那么使用準備好的語句有什么選擇?

第二個問題

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

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

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

第三個問題:

我是否應該逃避/保護 $_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'];

這段代碼解釋了:

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

這個塊的問題是當 $_SESSION['UID']; 通過基于 $_SESSION[' 的數據庫分配時，我是否應該逃避/保護 SQL 注入real_name'];

感謝您花時間閱讀這一大塊內容.

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

<塊引用>

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

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

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

因此，變量的來源或其價值永遠不應成為您的關注點.但只有它在查詢中的位置:

• 字符串必須用引號括起來并轉義這些引號.
• 數字必須轉換為它的類型.
• 標識符必須用反引號括起來，并將這些反引號加倍

當查詢的 static 部分在腳本中硬編碼時，我們不會使用如此嚴格的標準 - 例如，我們不會將每個標識符都包含在反引號中.
但是當涉及查詢的動態部分時，應用格式規則應該是嚴格的規則，因為我們無法確定變量內容.

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

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.

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