什么时候在PHP Mysqli中使用准备好的语句? -用户表单搜索输入与选择查询 [英] When to use prepared statements in PHP Mysqli? - User form search input vs select queries

问题描述

我试图了解何时应该在php/mysqli中使用准备好的语句.每个php/mysqli查询都应该使用准备好的语句，还是仅使用涉及用户输入的查询和实例……例如要求用户输入数据以在数据库中进行搜索的html表单?

I am trying to understand when I should use prepared statements in php/mysqli. Should every php/mysqli query use prepared statements or just queries and instances where user input is involved ... such as an html form that asks a user to enter data to search within a database?

我正在将旧的php5/mysql代码迁移到php7/mysqli.我有许多查询mysql db的php文件.我想澄清一下是否需要为连接到mysql db的每个php文件使用准备好的语句...例如，通过"php require"引用的php文件，并包括简单的sql select语句以呈现图像和html链接页面?

I am migrating my old php5/mysql code to php7/mysqli. I have many php files that query a mysql db. I would like clarification if I need to use prepared statements for every php file that connects to a mysql db ... for example php files that are referenced via "php require" and include simple sql select statements to render images and links to a html page?

<?php

//establish connection

$con = new mysqli('localhost','uid','pw','db');

//check connection

if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);  
}

//search variable that stores user input

$search = "%{$_POST['search']}%";

//prepare, bind and fetch

$stmt = $con->prepare("SELECT image, caption FROM `tblimages`
WHERE catid = 3 AND caption LIKE ? order by caption ASC");
$stmt->bind_param("s", $search);
$stmt->execute();
$stmt->bind_result($image,$caption);

while ($stmt->fetch()) {
echo "{$image} <br> {$caption} <br>";    
}

$stmt->close();

//close database connection

mysqli_close($con);

?>

上面的代码有效，并且是我使用过的第一个准备好的语句.它从表单中获取用户输入(输入搜索词的空白框-POST)并搜索db ...，然后将结果呈现到html页面.这似乎是对预准备语句的逻辑使用.但是...我还有其他php文件，用户可以在其中从表单的下拉框中选择数据以呈现结果(用户不像上面那样在搜索框中输入数据).我是否也为该实例使用准备好的语句?另外，我是否对通过"php require"引用的php文件使用准备好的语句，并包括简单的sql select语句来呈现图像和到html页面的链接?我还没有找到使用准备好的语句来防止sql注入的特定实例的说明.欢迎任何澄清或参考.

The code above works and is the first I've ever used prepared statements. It takes user input from a form (blank box to enter a search term - POST) and searches a db ... then renders results to an html page. This seems like a logical use of prepared statements. However ... I have other php files where users select data from a drop down box in a form to render a result (the user does not enter data into a search box like above). Do I use prepared statements for that instance as well? Plus do I use prepared statements for php files that are referenced via "php require" and include simple sql select statements to render images and links to a html page? I've yet to find clarification of the specific instances to use prepared statements to prevent sql injections. Any clarification or references welcome.

推荐答案

简短答案:始终使用准备好的语句.

Short answer: Always use prepared statements.

详细答案:

准备好的语句将您的数据与SQL命令分开.它们由 PDO 或

Prepared statements separate your data from SQL commands. They are provided by PDO or by MySQLi. Their biggest advantage is that it is impossible to have SQL injection if your data is treated as data. Another advantage is that you can execute the same query over and over again with different set of data, which might be better for your performance and often keeps your code cleaner.

但是，有时您希望基于用户的选择或操作进行某种动态查询.您可能知道表名和列名不是数据，而是SQL查询的一部分，因此不能将它们分开.这样，准备好的语句的替代方法是拥有一个可能值的白名单，并且仅允许根据该白名单对用户输入进行验证.

However, there are times when you would like to have some kind of dynamic query based on user's selection or actions. As you probably know table and column names are not data, but part of SQL query, therefore you can't keep them separated. The alternative to prepared statements then is to have a white list of possible values and only allow user input validated against the white list.

您可能会问什么是 query ， real_query ，

You might ask what are query, real_query, multi_query and PDO::exec good for?
As the PHP Manual shows they are good at times when you only need to execute constant query without any variables or when you have a query which can't be prepared. e.g.

$mysqli->query('SELECT Name FROM City LIMIT 10');
$pdo->exec('DELETE FROM fruit');
$mysqli->multi_query('DELETE FROM fruit; DELETE FROM pets;');

如果您知道数据的类型和值怎么办?您还应该准备/绑定吗?
是的！养成绑定所有与SQL查询一起使用的数据的习惯.没有理由例外.在代码中跟踪这些异常要困难得多，并且始终确保不要用一些未知的输入覆盖安全"值.

What if you know the type and values of your data? Should you also prepare/bind?
Yes! Get into a habit of binding all data going with SQL query. There is no reason to make exceptions. It is much more difficult to trace those exceptions in your code and always be sure you do not overwrite the "safe" value with some unknown input.

如果您仍然不确定如何使用准备好的语句，或者您认为它们太复杂(不是)，可以在

If you are still not sure how to use prepared statements or you think that they are too complicated (they are not) you can take a look at an amazing PHP tutorial at https://phpdelusions.net

这篇关于什么时候在PHP Mysqli中使用准备好的语句? -用户表单搜索输入与选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！