我什么时候应该使用准备好的语句? [英] When should I use prepared statements?

问题描述

最初我使用mysql_connect 和mysql_query 来做事.然后我学习了 SQL 注入，所以我正在尝试学习如何使用准备好的语句.我了解 PDO 类的准备和执行功能如何有助于防止 SQL 注入.

仅当用户输入存储到数据库时才需要准备好的语句吗?仍然使用 mysql_num_rows 是否可以，因为我并没有真正冒着使用此功能被黑客入侵的风险?还是使用准备好的语句来执行此操作更安全?我应该对涉及使用 MySQL 的所有内容使用准备好的语句吗?为什么?

解决方案

tl/dr

总是.100％的时间，使用它.总是;即使你不需要使用它.仍然使用它.

<小时>

mysql_* 函数已弃用.(

<小时>

更多阅读

• 如何防止 SQL-在 php 中注入?
• 什么是 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.

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

Always. 100% of the time, use it. Always; and even if you don't need to use it. USE IT STILL.

---

mysql_* functions are deprecated. (^{Notice the big red box?})

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()

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:

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.

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']

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.

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();

^{Taken from PHP Manual for PDO Prepared Statements}

---

More Reading

• How can I prevent SQL-injection in php?
• What is SQL-injection? (^{Simple Terms})

这篇关于我什么时候应该使用准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！