Php USES PDO to query Mysql to avoid the injection risk of SQL

When we use the traditional mysql_connect, mysql_query methods to connect to the query database, if the filtering is not strict, there is a risk of injection of SQL, resulting in a site attack, out of control. Although it is possible to filter user-submitted values using the mysql_real_escape_string() function, there are drawbacks. The sql injection risk can be avoided by using PHP's PDO extended prepare method.

PDO(PHP Data Object) is a major new addition to PHP5, because before PHP 5, php4/php3 were one-heap database extensions to connect to and process individual databases, such as php_mysql.dll. PHP6 will also be connected by default using PDO, and the mysql extension will be used as an auxiliary. Official: http: / / php net manual/en/book pdo. php

1. PDO configuration
Before using the PDO extension, enable this extension. In PHP.ini, remove the ";" before "extension= php_pdo.dll "; To connect to the database, remove the ";" in front of the PDO-related database extension. Number (php_pdo_mysql.dll), 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=db_demo","root","password");

The default is not a long connection. To use a database long connection, add the following parameters at the end:

$dbh = new PDO("mysql:host=localhost;dbname=db_demo","root","password","array(PDO::ATTR_PERSISTENT => true)");
$dbh = null; //( The release of )

3. Set PDO properties

1) PDO has three error handling modes:

The & # 8226; PDO::ERrmODE_SILENT does not display an error message, only sets the error code
The & # 8226; PDO::ERrmODE_WARNING displays warning error
The & # 8226; PDO::ERrmODE_EXCEPTION throws an exception

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

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

When set to PDO::ERrmODE_SILENT, you can get an error message by calling errorCode() or errorInfo(), as well as in other cases.

2) because different databases handle the case of the field names returned differently, PDO provides the PDO::ATTR_CASE Settings (including PDO::CASE_LOWER, PDO::CASE_NATURAL, PDO::CASE_UPPER) to determine the case of the field names returned.

3) specify the NULL value returned by the database in php by setting 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 primarily used for operations that return recorded results, especially SELECT operations
PDO::exec() is mainly for operations that do not return a result set, such as INSERT, UPDATE, etc
PDO::prepare() is primarily a pre-processing operation, which requires $rs- > execute() is used to execute the SQL statement in the preprocessing. This method can bind parameters, which is quite powerful (to prevent the injection of sql).
PDO::lastInsertId() returns the last insert operation, and the primary key column type is the last self-increment ID
PDOStatement::fetch() is used to get a record
PDOStatement::fetchAll() is to get all recordset to 1 collection
PDOStatement::fetchColumn() is a field that gets the result specifying the first record. The default is the first field
PDOStatement::rowCount() : is mainly used as a result set for DELETE ::query() and PDO::prepare() operations affecting DELETE, INSERT, UPDATE operations. It is invalid for PDO::exec() methods and SELECT operations.

5. PDO operates MYSQL database instances

<?php
$pdo = new PDO("mysql:host=localhost;dbname=db_demo","root","");
if($pdo -> exec("insert into db_demo(name,content) values('title','content')")){
echo " Insert successfully! ";
echo $pdo -> lastinsertid();
}
?>

<?php
$pdo = new PDO("mysql:host=localhost;dbname=db_demo","root","");
$rs = $pdo -> query("select * from test");
$rs->setFetchMode(PDO::FETCH_ASSOC); // Associative array form 
//$rs->setFetchMode(PDO::FETCH_NUM); // Number index array form 
while($row = $rs -> fetch()){
print_r($row);
}
?>

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

Count how many rows there are

$sql="select count(*) from test";
$num = $dbh->query($sql)->fetchColumn();

prepare way

$stmt = $dbh->prepare("select * from test");
if ($stmt->execute()) {
 while ($row = $stmt->fetch()) {
     print_r($row);
 }
}

Prepare parameterized queries

$stmt = $dbh->prepare("select * from test where name = ?");
if ($stmt->execute(array("david"))) {
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
     print_r($row);
 }
}

[here's the main point, how to prevent sql injection]

When using PDO to access the MySQL database, the real real prepared statements is not used by default. To solve this problem, you must disable prepared statements emulation. Here's an example of creating a link using PDO:

$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

The 1 line setAttribute () is mandatory and tells PDO to disable the mock preprocessing statement and to use real parepared statements. This ensures that the SQL statements and the corresponding values are not parsed by PHP until they are passed to the mysql server (all possible malicious SQL injection attacks are disabled). Although you can set the character set properties in the configuration file (charset=utf8), it is important to note that the older version of PHP ( < 5.3.6) character parameters are ignored in DSN.

Let's look at a complete code usage example:

$dbh = new PDO("mysql:host=localhost; dbname=demo", "user", "pass");
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // disable prepared statements Simulation effect 
$dbh->exec("set names 'utf8'");
$sql="select * from test where name = ? and password = ?";
$stmt = $dbh->prepare($sql);
$exeres = $stmt->execute(array($testname, $pass));
if ($exeres) {
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
     print_r($row);
 }
}
$dbh = null;

The code above protects against an sql injection. Why is that?

When prepare() is called, the query has been sent to the database server, and there are only placeholders? Send in the past, no data submitted by the user; When execute() is called, the values submitted by the user are passed to the database. They are passed separately, and the two are independent. The SQL attacker has no point of opportunity.

However, it is important to note that PDO does not protect you against injection of SQL

1. You can't use placeholders? Replace 1 set of values, such as:

SELECT * FROM blog WHERE userid IN ( ? );

2. You cannot use placeholders to replace table or column names, such as:

SELECT * FROM blog ORDER BY ?;

3. You can't use placeholders? Instead of any other SQL syntax, such as:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;