使用 MySQLI 正确转义 |查询准备好的语句

2021-12-25 00:00:00 php mysqli

我读过这个:

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

在以下帖子中:带有 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.

相关文章