How to use PDO to query mysql to avoid SQL injection

When using the traditional mysql_connect and mysql_query methods to connect and query the database, if the filtering is not strict, there is a risk of SQL injection. Although the mysql_real_escape_string() function can be used to filter user-submitted values, it also has flaws. By using the prepare method of PHP's PDO extension, you can avoid the risk of sql injection.

PDO (PHP Data Object) is a major new feature added to PHP5, because before PHP 5, php4/php3 had a bunch of database extensions to connect and connect with each database. Processing, such as php_mysql.dll. PHP6 will also use PDO to connect by default, and the mysql extension will be used as an auxiliary. Official address: http://php.net/manual/en/book.pdo.php

1. PDO configuration

Before using the PDO extension, you must first enable this extension. In php.ini, remove the ";" in front of "extension=php_pdo.dll". If you want to connect to the database, you also need to remove the PDO-related database extension. ";" (usually php_pdo_mysql.dll is used), and then restart the Apache server.

extension=php_pdo.dll 
extension=php_pdo_mysql.dll

2. PDO connects to mysql database

$dbh = new PDO("mysql:host=localhost;dbname=mydb","root","password");

The default is not a long connection. If you want to use a long connection to the database, you can add it at the end Add the following parameters:

$dbh = new PDO("mysql:host=localhost;dbname=mydb","root","password","array(PDO::ATTR_PERSISTENT => true) "); 
$dbh = null; //(释放)

3. PDO setting properties

PDO has three error handling methods:

PDO::ERrmODE_SILENT does not display error messages, only sets error codes

PDO::ERrmODE_WARNING displays warning errors

PDO::ERrmODE_EXCEPTION throws an exception

You can use the following statement to set the error handling method to throw an exception

$db->setAttribute(PDO::ATTR_ERrmODE, PDO::ERrmODE_EXCEPTION);

Because different database pairs return The case of field names is handled differently, so PDO provides the PDO::ATTR_CASE setting item (including PDO::CASE_LOWER, PDO::CASE_NATURAL, PDO::CASE_UPPER) to determine the case of the returned field name.

Specify the corresponding value in php for the NULL value returned by the database by setting the PDO::ATTR_ORACLE_NULLS type (including PDO::NULL_NATURAL, PDO::NULL_EmpTY_STRING, PDO::NULL_TO_STRING).

4. Common PDO methods and their applications

PDO::query() is mainly used for records Operations that return results, especially SELECT operations

PDO::exec() is mainly for operations that do not return a result set, such as INSERT, UPDATE and other operations

PDO::prepare() is mainly a preprocessing operation. You need to use $rs->execute() to execute the SQL statement in the preprocessing. This method can bind parameters and is more powerful (preventing sql injection Just rely on this)

PDO::lastInsertId() returns the last insert operation, the primary key column type is the last auto-increment ID

PDOStatement::fetch() is used to obtain a record

PDOStatement::fetchAll() is used to obtain all record sets into a collection

PDOStatement::fetchColumn() is a field of the first record specified in the fetch result. The default is the first field.

PDOStatement::rowCount(): Mainly used The result set affected by DELETE, INSERT, and UPDATE operations on PDO::query() and PDO::prepare() is invalid for the PDO::exec() method and SELECT operation.

5.PDO operation MYSQL database instance

<?php 
$pdo = new PDO("mysql:host=localhost;dbname=mydb","root",""); 
if($pdo -> exec("insert into mytable(name,content) values(&#39;fdipzone&#39;,&#39;123456&#39;)")){ 
echo "insert success"; 
echo $pdo -> lastinsertid(); 
} 
?>

<?php 
$pdo = new PDO("mysql:host=localhost;dbname=mydb","root",""); 
$rs = $pdo -> query("select * from table"); 
$rs->setFetchMode(PDO::FETCH_ASSOC); //关联数组形式
//$rs->setFetchMode(PDO::FETCH_NUM); //数字索引数组形式
while($row = $rs -> fetch()){ 
    print_r($row); 
} 
?>

<?php
foreach( $db->query( "SELECT * FROM table" ) as $row )
{
    print_r( $row );
}
?>

Statistics on how many rows of data there are:

<?php
$sql="select count(*) from table";
$num = $dbh->query($sql)->fetchColumn();
?>

prepare method:

<?php
$query = $dbh->prepare("select * from table");
if ($query->execute()) {
    while ($row = $query->fetch()) {
        print_r($row);
    }
}
?>

prepare parameterized query:

<?php
$query = $dbh->prepare("select * from table where id = ?");
if ($query->execute(array(1000))) { 
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);
    }
}
?>

When using PDO to access the MySQL database , real prepared statements are not used by default. To solve this problem, you must disable the emulation effects of prepared statements. The following is an example of using PDO to create a link:

<?php
$dbh = new PDO(&#39;mysql:dbname=mydb;host=127.0.0.1;charset=utf8&#39;, &#39;root&#39;, &#39;pass&#39;);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
?>

setAttribute()这一行是强制性的，它会告诉 PDO 禁用模拟预处理语句，并使用 real parepared statements 。这可以确保SQL语句和相应的值在传递到mysql服务器之前是不会被PHP解析的（禁止了所有可能的恶意SQL注入攻击）。

虽然你可以配置文件中设置字符集的属性(charset=utf8)，但是需要格外注意的是，老版本的 PHP（ < 5.3.6）在DSN中是忽略字符参数的。

完整的代码使用实例：

<?php
$dbh = new PDO("mysql:host=localhost; dbname=mydb", "root", "pass");
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //禁用prepared statements的仿真效果
$dbh->exec("set names &#39;utf8&#39;"); 
$sql="select * from table where username = ? and password = ?";
$query = $dbh->prepare($sql); 
$exeres = $query->execute(array($username, $pass)); 
if ($exeres) { 
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);
    }
}
$dbh = null;
?>

上面这段代码就可以防范sql注入。为什么呢？

当调用 prepare() 时，查询语句已经发送给了数据库服务器，此时只有占位符 ? 发送过去，没有用户提交的数据；当调用到 execute()时，用户提交过来的值才会传送给数据库，它们是分开传送的，两者独立的，SQL攻击者没有一点机会。

但是我们需要注意的是以下几种情况，PDO并不能帮助你防范SQL注入。

不能让占位符 ? 代替一组值，这样只会获取到这组数据的第一个值，如：

select * from table where userid in ( ? );

如果要用in來查找，可以改用find_in_set()实现

$ids = '1,2,3,4,5,6';
select * from table where find_in_set(userid, ?);

不能让占位符代替数据表名或列名，如：

select * from table order by ?;

不能让占位符 ? 代替任何其他SQL语法，如：

select extract( ? from addtime) as mytime from table;

本篇文章如何使用PDO查询mysql避免SQL注入的方法，更多相关内容请关注php中文网。

相关推荐：

关于php 双向队列类的讲解

php heredoc 与 nowdoc之间的区别与特点

关于HTML5 localStorage and sessionStorage 之间的区别

The above is the detailed content of How to use PDO to query mysql to avoid SQL injection. For more information, please follow other related articles on the PHP Chinese website!