MySQL 查询中是否应该引用用户输入的数字来帮助避免 SQL 注入攻击？

发布于 2024-11-14 11:20:15 字数 936 浏览 4 评论 0 原文

假设我在页面上有一个表格询问某人的年龄。他们输入年龄并点击提交。以下 php 代码处理表单提交：（age 是 db 表中的 int 字段。）

$Number = mysqli_real_escape_string($dbc, $_POST["age"]);
$Query = "INSERT INTO details (age) VALUES ($Number)";
$Result = mysqli_query($dbc, $Query);

除此之外，将用户输入括在单引号中（即使它不是字符串）是否可以获得任何好处？像这样：

...
$Query = "INSERT INTO details (age) VALUES ('$Number')";  <-- quotes
...

执行 SELECT 怎么样？这是：

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = '$ID'";
$Result = mysqli_query($dbc, $Query);

比：

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = $ID";      <-- no quotes
$Result = mysqli_query($dbc, $Query);

注意：我知道准备好的语句，并且通常在字符串连接上使用它们，但这是我正在处理的遗留代码。我想尽我所能保护它。

原文

Should numbers from user input be quoted in MySQL queries to help avoid SQL injection attacks?

Say i have a form on a page asking for someone's age. They enter their age and hit submit. The following php code deals with the form submission: (age is an int field in the db table.)

$Number = mysqli_real_escape_string($dbc, $_POST["age"]);
$Query = "INSERT INTO details (age) VALUES ($Number)";
$Result = mysqli_query($dbc, $Query);

Instead of this, is there anything to be gained to enclosing the user input in single quotes, even though it is not a string? Like this:

...
$Query = "INSERT INTO details (age) VALUES ('$Number')";  <-- quotes
...

What about performing a SELECT? Is this:

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = '$ID'";
$Result = mysqli_query($dbc, $Query);

better than:

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = $ID";      <-- no quotes
$Result = mysqli_query($dbc, $Query);

NOTE: I am aware of prepared statements and usually use them over string concatenation but this is legacy code i'm dealing with. I want to secure it as best as i can.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

断桥再见 2024-11-21 11:20:15

如果添加数字，请使用 intval/floatval 函数，不要使用 mysql_real_escape_string 函数。

对于您使用 mysql_real_escape_string 的所有内容，您必须使用引号，例如：

$input = "foo'bar";
$input = mysql_real_escape_string($input);
//foo\'bar
mysql_query("SELECT $input");
//SELECT foo\'bar
//which is still an SQL syntax error.

If you add numbers, use the intval/floatval functions, don't use mysql_real_escape_string for those.

For everything you use mysql_real_escape_string for, you must use quotes, example:

$input = "foo'bar";
$input = mysql_real_escape_string($input);
//foo\'bar
mysql_query("SELECT $input");
//SELECT foo\'bar
//which is still an SQL syntax error.

回复收藏 0 原文

遗忘曾经 2024-11-21 11:20:15

你真的应该使用 sprintf，即使在遗留代码中需要 2 分钟来修改，并且在我看来完全值得花时间。

无耻地从 php.net 中窃取：

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
                 WHERE  firstname='%s' AND lastname='%s'",
                 mysql_real_escape_string($firstname),
                 mysql_real_escape_string($lastname));

// Perform Query
$result = mysql_query($query);

您的查询现在非常安全，不会将错误的类型传递到其字段和未转义的字符。

You really shoud use sprintf, even if in legacy code it takes 2 mins to modify and is in my opinion totally worth the time.

Shamelessly ripped from php.net:

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
                 WHERE  firstname='%s' AND lastname='%s'",
                 mysql_real_escape_string($firstname),
                 mysql_real_escape_string($lastname));

// Perform Query
$result = mysql_query($query);

Your query is now pretty much safe from being passed the wrong types to it's fields and unescaped caracters.

回复收藏 0 原文