为什么此MySQLI Prepared语句允许SQL注入? [英] Why does this MySQLI prepared statement allow SQL injection?

问题描述

当我今天在教学生如何防止SQL注入时，我有些尴尬.在专业项目中，我已经使用准备好的语句/参数化查询作为防止SQL注入的一层措施(尽管我从未专业地使用过mySQL).从理论上讲，我认为使用准备好的语句时不可能进行SQL注入.

As I was teaching students how to prevent SQL injection today, I was mildly embarrassed. In professional projects I've used prepared statements / parameterized queries as one layer of prevention against SQL injection (although I've never used mySQL professionally). In theory, I thought SQL injection was impossible when using a prepared statement.

但是后来这起作用了...

But then this worked...

$Search = $_GET['s'];
$stmt = $mysqli->prepare("SELECT * FROM products WHERE id = ?");
$stmt->bind_param("i", $Search);
$stmt->execute();
$Results = $stmt->get_result();

如果我传递参数?s = 1 OR 1 = 1"，那么我可以获得所有产品的完整清单.我仍然无法在末尾插入另一个查询，但是我对为什么在mysql/php中可以使用这种类型的基本SQL注入感到困惑.我假设参数"1 OR 1 = 1"将被拒绝，因为它不是数字的(显然，我可以运行该数字检查...).

If I pass the parameter "?s=1 OR 1=1" then I can get a full listing of all products. I still can't insert another query at the end, but I am confused about why this type of basic SQL injection is possible in mysql/php. I assumed that the parameter "1 OR 1=1" would be rejected because it isn't numeric (obviously I could run that numeric check ...).

任何人都可以解释为什么这行得通，以及我对php/mysql中的准备好的语句不了解吗?

Can anyone explain why this works, and what I don't understand about prepared statements in php/mysql?

我的学校计算机具有开箱即用的Zend WAMP安装.

My school computer has an out-of-the-box Zend WAMP installation btw.

这是引起我困惑的字符串值:

This is the string value that is causing my confusion:

$Search = "1 OR 1=1";

推荐答案

没关系，这里没有任何SQL注入，您看不到所有产品的列表(至少您提供的代码无法显示它)

That's ok, no any kind of SQL injection here, you do not see list of all products (at least code you provided cannot show it)

您无需强制转换为int，让我们更深入地了解bind_param的实际作用:

you do not need to cast to int, lets go deeper what bind_param actually do:

它的字符串"i"表示1个integer自变量

it has string "i" which means 1 integer argument

您正在从$ _GET传递值为string

you're passing value from $_GET which is string

bind_param尝试将String转换为int，因此请检查以下php代码:

bind_param tries to convert String to int, so check this php code:

echo intval('a', 10); // output 0
echo intval('1a', 10); // output 1
echo intval('12a', 10); // output 12
echo intval('b1', 10); // output 0
echo intval('1 or 1=1', 10); // output 1
echo intval("?s=1 OR 1=1", 10); // output 0

那么，您输出id = 1的产品，也许您有一些?

so, you output products with id=1, maybe you have some of them?

这篇关于为什么此MySQLI Prepared语句允许SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！