我们应该始终绑定我们的SQL语句吗? [英] Should we always bind our SQL statements?
问题描述
我一直在研究PDO的bindValue().我知道用PDO准备SQL语句可以防止发生SQL注入.
I have been researching into PDO's bindValue(). I know that preparing my SQL statements with PDO is keeping SQL injections from happening.
代码示例:
$stmt = $dbh->prepare('SELECT * FROM articles WHERE id = :id AND title = :title');
$stmt->bindValue(':id', PDO::PARAM_INT);
$stmt->bindValue(':title', PDO::PARAM_STR);
$stmt->execute();
通过将ID绑定为数字，并且标题是字符串，我们可以限制当有人尝试在代码中进行SQL注入时造成的损害.
By binding the ID as a number, and the Title was a string, we can limit the damage done when someone tries to do an SQL injection within the code.
是否应该始终将值与PDO::PARAM_绑定，以便我们可以限制在SQL注入中可以从数据库中提取的内容?在执行我们的bindValue()时，这是否会增加PDO的安全性?
Should we always bind our values with a PDO::PARAM_ so we can limit what can be pulled from the database in an SQL injection? Does this add more security with PDO when doing our bindValue()?
推荐答案
一个问题有两个.重要的是不要混淆它们
There are two questions in one. It is essential not to confuse them
- 我们是否应该始终使用占位符表示查询中的变量数据?
- 我们应该始终在应用代码中使用某些功能来遵循上述规则吗?
另外，根据开头帖子下方的评论中的澄清 ，可以看到第三个问题: - 我们应该始终使用第三个参数，还是可以让PDO默认将所有参数绑定为字符串?
- Should we always use a placeholder to represent a variable data in the query?
- Should we always use certain function in the application code to follow the above rule?
Also, from the clarification in the comments under the opening post, the third question can be seen: - Should we always use third parameter, or it's OK to let PDO bind all the parameters as strings by default?
1.对于第一个问题，答案是绝对肯定的-是.
在第二个版本中，出于代码完整性和干燥性的考虑-
1. For the first question the answer is absolutely and definitely - YES.
While for the second one, for sake of code sanity and DRYness -
有很多方法可以避免手动绑定.其中一些是:
There are many ways to avoid manual binding. Some of them are:
-
ORM是简单CRUD操作的出色解决方案，必须在现代应用程序中使用.它将完全向您隐藏SQL，在幕后进行绑定:
ORM is an excellent solution for the simple CRUD operations and must have in a modern app. It will hide SQL from you completely, doing the binding behind the scenes:
$user = User::model()->findByPk($id);
查询生成器也是一种解决方法，它在某些PHP运算符中伪装了SQL，但再次将绑定隐藏在幕后:
Query Builder is also the way to go, disguising SQL in some PHP operators but again hiding the binding behind the scenes:
$user = $db->select('*')->from('users')->where('id = ?', $id)->fetch();
某些抽象库可能会通过 type-hinted-placeholders 来处理传递的数据，从而再次隐藏实际的绑定:
some abstraction library may take care of the passed data by means of type-hinted-placeholders, hiding the actual binding again:
$user = $db->getRow("SELECT * FROM users WHERE id =?i", $id);
如果您在上个世纪仍在使用PHP，并且在整个代码中都使用了原始PDO，那么您可以在execute()中传递变量，而仍然节省了很多输入时间:
if you are still using PHP in the last century ways, and have raw PDO all over the code - then you can pass your variables in execute(), still saving yourself a lot of typing:
$stmt = $dbh->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$id]);
$user = $stmt->fetch();
从第三个问题开始-只要您将数字绑定为字符串(但不是相反)-
As of the third question - as long as you are binding numbers as strings (but not the opposite!) -
因为mysql始终会将您的数据转换为正确的类型.我所知道的唯一一种情况是LIMIT子句，在该子句中您无法将数字格式化为字符串-因此，唯一相关的情况是一个
as mysql will always convert your data to the proper type. The only case known to me, is a LIMIT clause where you cannot format number as a string - thus, the only related case is one when PDO is set in emulation mode and you have to pass a parameter in LIMIT clause. In all other cases you can omit third parameter, as well as explicit call to bindValue() without any problem.
这篇关于我们应该始终绑定我们的SQL语句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
