什么时候应该使用准备好的语句? [英] When should I use prepared statements?
问题描述
最初，我使用mysql_connect和mysql_query做事情.然后我学习了SQL注入，因此我试图学习如何使用准备好的语句.我了解PDO类的prepare和execute函数如何有助于防止SQL注入.
Originally I used mysql_connect and mysql_query to do things. Then I learned of SQL injection, so I am trying to learn how to use prepared statements. I understand how the prepare and execute functions of the PDO class are useful to prevent SQL injection.
仅当用户输入存储到数据库中时才需要准备好的语句吗?仍然使用mysql_num_rows可以吗，因为使用该功能确实不会冒被黑客入侵的风险?还是使用准备好的语句执行此操作更安全?我应该为涉及MySQL的所有事情使用准备好的语句吗?为什么?
Are prepared statements only necessary when a users input is stored into a database? Would it be okay to still use mysql_num_rows, since I don't really run the risk of being hacked into by using this function? Or is it more secure to use prepared statements to do this? Should I use prepared statements for everything that involves using MySQL? Why?
推荐答案
tl/dr
mysql_*函数已被弃用. ( 是否注意到红色的大方框? )
mysql_* functions are deprecated. (Notice the big red box?)
警告此扩展在PHP 5.5.0中已弃用，已将其删除. 在PHP 7.0.0中.而是使用 MySQLi 或 MySQL:选择API 指南和
Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:
- mysqli_connect()
- PDO::__construct()
最好使用 PDO > 或 MySQLi .使用准备好的语句时，这些 2 都可以用作兼容库.
You'd be better off using PDO or MySQLi. Either of those 2 will suffice as compatible libraries when using prepared statements.
在没有准备好的陈述/消毒的情况下信任用户的输入就像将您的汽车放在不安全的地方，未锁定并且钥匙在点火开关中一样.您基本上是说，请进来拿我的礼物
Trusting user input without prepared statements/sanitizing it is like leaving your car in a bad neighborhood, unlocked and with the keys in the ignition. You're basically saying, just come on in and take my goodies
您应该从不，我的意思是从不信任用户输入.除非您要这样:
You should never, and I mean never, trust user input. Unless you want this:
关于数据的存储和评论中所述，您可以从不，并且永远不要信任任何与用户相关的输入.除非您101％确信用于操纵所述数据库/值的数据已硬编码到您的应用程序中，否则您必须使用准备好的语句.
In reference to the data and storing it, as stated in the comments, you can never and should never trust any user related input. Unless you are 101% sure the data being used to manipulate said databases/values is hard-coded into your app, you must use prepared statements.
现在介绍为什么要使用准备好的语句.这很简单.防止SQL注入，但是要以最直接的方式进行.准备好的语句的工作方式很简单，它将查询和数据一起发送，但是是分开的(如果有意义的话哈哈)-我要做什么就是这个意思:
Now onto why you should use prepared statements. It's simple. To prevent SQL Injection, but in the most straight forward way possible. The way prepared statements work is simple, it sends the query and the data together, but seperate (if that makes sense haha) - What I mean is this:
Prepared Statements
Query: SELECT foo FROM bar WHERE foo = ?
Data: [? = 'a value here']
与其前身相比，您在其中截断了数据查询，然后将其作为一个整体发送，这意味着将其作为单个事务执行，从而导致SQL注入漏洞.
Compared to its predecessor, where you truncated a query with the data, sending it as a whole - in turn, meaning it was executed as a single transaction - causing SQL Injection vulnerabilities.
这是一个伪PHP PDO示例，向您展示准备好的语句/绑定的简单性.
And here is a pseudo PHP PDO example to show you the simplicity of prepared statements/binds.
$dbh = PDO(....); // dsn in there mmm yeahh
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// insert one row
$name = 'one';
$value = 1;
$stmt->execute();
更多阅读内容
这篇关于什么时候应该使用准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
