使用MySQLI正确转义|查询准备好的语句 [英] Properly Escaping with MySQLI | query over prepared statements

问题描述

我已阅读:

将帮助您避免注射. Beause转义只是一种字符串格式化工具，无论如何都不是防止注入的工具. 去搞清楚. 但是，转义与准备好的语句有一些共同点: 他们俩都不能保证您注射 您仅针对臭名昭著的用户输入"来使用它，尽管有数据源，但它并不是构建ANY查询的严格规则. 如果您需要插入的不是数据，而是标识符或关键字.

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.

在以下帖子上:所以我的问题是使用:

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

不提供针对SQL注入的保护吗?

does not provide protection against SQL Injection?

我想使用$mysqli->query();，所以我可以使用fetch_array(MYSQLI_ASSOC);.坦率地说，我不知道在使用prepared语句后如何将结果作为数组来获取.

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");
}

如real_escape_string手册中所述

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

上面的列表:

注意 安全性:默认字符集 必须在服务器级别或使用API​​函数mysqli_set_charset()设置字符集，才能影响mysqli_real_escape_string().有关更多信息，请参见字符集的概念部分.

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.

链接到: http://php.net/manual/zh-CN /mysqli.set-charset.php

我的总体问题可以分为三个选项，第一个是要求为prepared语句提供fetch_array()等效项，由于准备好的语句将数据作为原始数据发送，因此它将提供完整的SQL注入预防功能.

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.

第一个问题采用以下格式:

我将查询用作:

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

哪个返回:

Array([Status] => 1)

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

哪个返回:

致命错误:在第17行的/var/www/New/API/Constants.php中的非对象上调用成员函数fetch_row()

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

当我尝试fetch_array()时会出现同样的情况，我知道它不能与准备好的语句一起使用.

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?

第二个问题

如果我将常规查询"用作:

If I use My Usual Query as:

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

使我能够使用fetch_array();的数据是否可以从SQL注入中正确保护?

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

第三个问题:

我应该为$_SESSION['UID'];转义/防止SQL注入，因为这是在以下庄园中分配的:

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'];

此代码段说明:

用户使用用户名&登录密码，该文件根据$_SESSION['real_name'];从数据库获取信息 并将结果添加到$ _SESSION数组中，并将每个结果添加到不同的键中.

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.

此块的问题是，当基于$_SESSION['real_name'];

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. 是的，但这是非常不好的做法:
   • 在这种情况下将为您提供帮助，但在这种情况下仅并以其他方式欺骗您
   • 手动转义只是愚蠢的，最好让驾驶员为您完成

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

使用$mysqli->real_escape_string($Var);不能防止SQL注入吗?

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

我没有改变主意:没错.
仅当将结果值括在引号中(并使用mysqli_set_charset()设置严格的编码以严格设置)时，它才会起作用.

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

看起来，SQL注入并不是必需的，它本身就已经存在，但这仅仅是结果.查询格式不正确的后果.
创建查询时，您必须正确设置其每个部分的格式.不是因为任何注入"而是为了它.当您要在查询中插入字符串时，必须将其放在引号中，否则会出现语法错误.当您要在查询中插入字符串时，您必须转义使用这些引号来分隔该字符串，否则会出现语法错误.等等.您应该关注的是正确的格式设置，而不要害怕有关注入的故事.而且只要您根据其类型正确设置了每个动态查询部分的格式-不可能进行注入

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:

• 字符串必须用引号引起来，并且必须将这些引号转义.
• 数字必须转换为它的类型.
• 标识符必须放在反引号中，并且这些反引号要加倍

当要进行查询的 static 部分(在脚本中进行硬编码)时，我们不会使用如此严格的标准-例如，我们不会将每个标识符都放在反引号中.
但是，当要进行查询的 dynamic 部分时，应用格式设置规则应该是严格的规则，因为我们不能确定变量的内容.

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正确转义|查询准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！