PHP to use PDO query MySQL to avoid SQL injection risk method _php Skills

When we use the traditional mysql_connect, mysql_query method to connect query database, if the filter is not strict, there is SQL injection risk, resulting in the site was attacked, out of control. Although you can use the mysql_real_escape_string () function to filter user-submitted values, there are also drawbacks. Using the PDO extended prepare method of PHP, you can avoid the risk of SQL injection.

PDO (PHP Data Object) is a major feature of PHP5 's new addition, since php4/php3 in PHP 5 are a bunch of database extensions to connect and process with individual databases, such as Php_mysql.dll. PHP6 will also default to the use of PDO connection, MySQL extension will be used as a secondary. Official: http://php.net/manual/en/book.pdo.php

1, PDO configuration
Before using PDO extensions, first enable this extension, php.ini, remove the "Extension=php_pdo.dll" before the ";" Number, to connect to the database, you also need to remove the PDO-related database extensions before the ";" Number (typically used Php_pdo_mysql.dll), and then restart the Apache server.

Copy Code code as follows:

Extension=php_pdo.dll
Extension=php_pdo_mysql.dll

2, PDO connection MySQL database

Copy Code code as follows:

$DBH = new PDO ("Mysql:host=localhost;dbname=db_demo", "root", "password");

The default is not a long connection, and to use a database long connection, you need to add the following parameters at the end:

Copy Code code as follows:

$DBH = new PDO ("Mysql:host=localhost;dbname=db_demo", "root", "password", "Array (pdo::attr_persistent => true)");
$DBH = null; Release

3, PDO settings Properties

1) PDO has three kinds of error handling methods:

pdo::errmode_silent do not display an error message, just set the error code
pdo::errmode_warning Display Warning error
Pdo::errmode_exception throws an exception

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

Copy Code code as follows:

$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 (), of course, in other cases.

2 The PDO provides Pdo::attr_case settings items (including pdo::case_lower,pdo::case_natural,pdo::case_upper) because different databases have different case handling for the returned field names. To determine the case of the returned field name.

3) by setting the Pdo::attr_oracle_nulls type (including pdo::null_natural,pdo::null_empty_string,pdo::null_to_ STRING) to specify the numeric value that the null value returned by the database corresponds to in PHP.

4. Pdo common methods and their applications
Pdo::query () is used primarily for operations that have record results returned, especially select operations
Pdo::exec () is primarily for operations that are returned without a result set, such as INSERT, update, and so on
PDO::p Repare () is primarily a preprocessing operation that requires $rs->execute () to execute the SQL statements in the preprocessing, a method that can bind parameters and is powerful (prevent SQL injection on this)
Pdo::lastinsertid () returns the last insert operation, the primary key column type is the final self-increasing ID
Pdostatement::fetch () is used to get a record
Pdostatement::fetchall () is to get all the recordset to a collection
Pdostatement::fetchcolumn () is a field that gets the result to specify the first record, and the default is the first field
Pdostatement::rowcount (): Primarily for pdo::query () and PDO::p Repare () The result set that is affected by the delete, INSERT, and update operations, to Pdo::exec () Method and select operation are not valid.

5, PDO operation MySQL database instance

Copy Code code as follows:

<?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 success!" ";
echo $pdo-> Lastinsertid ();
}
?>

Copy Code code as follows:

<?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); Numeric indexed Array Form
while ($row = $rs-> fetch ()) {
Print_r ($row);
}
?>

Copy Code code as follows:

<?php
foreach ($db->query ("SELECT * from Feeds") as $row)
{
Print_r ($row);
}
?>

How many rows of data are counted

Copy Code code as follows:

$sql = "SELECT count (*) from Test";
$num = $dbh->query ($sql)->fetchcolumn ();

Prepare Way

Copy Code code as follows:

$stmt = $dbh->prepare ("SELECT * from Test");
if ($stmt->execute ()) {
while ($row = $stmt->fetch ()) {
Print_r ($row);
}
}

Prepare parameterized Query

Copy Code code as follows:

$stmt = $dbh->prepare ("SELECT * from test where name =?");
if ($stmt->execute ("David")) {
while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {
Print_r ($row);
}
}

"Here's the point, how to prevent SQL injection"

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

Copy Code code as follows:

$DBH = new PDO (' Mysql:dbname=dbtest;host=127.0.0.1;charset=utf8 ', ' user ', ' pass ');
$DBH->setattribute (Pdo::attr_emulate_prepares, false);

This line of setattribute () is mandatory, and it tells PDO to disable the impersonation preprocessing statement and use real parepared statements. This ensures that SQL statements and corresponding values are not parsed by PHP until they are passed to the MySQL server (all possible malicious SQL injection attacks are blocked). Although you can set the properties of the character set in the configuration file (Charset=utf8), it should be noted that the older version of PHP (< 5.3.6) ignores character parameters in DSN.

Let's take a look at a complete example of code usage:

Copy Code code as follows:

$DBH = new PDO ("Mysql:host=localhost; Dbname=demo "," User "," pass ");
$DBH->setattribute (Pdo::attr_emulate_prepares, false); To disable the emulation effect of prepared statements
$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 will protect you from SQL injection. Why, then?

When prepare () is invoked, the query statement has been sent to the database server, and only placeholders are available at this time. Sent in the past, no user submitted data; When the call to execute (), the value submitted by the user is transmitted to the database, they are transmitted separately, and the SQL attacker does not have a single chance.

But we need to be aware of the following situations where PDO does not help you prevent SQL injection

1, you can't let the placeholder? Instead of a set of values, such as:

Copy Code code as follows:

SELECT * FROM blog WHERE userid in (?);

2, you cannot let the placeholder replace the data table name or column name, such as:

Copy Code code as follows:

SELECT * from Blog order by?;

3, you can't let the placeholder? Instead of any other SQL syntax, such as:

Copy Code code as follows:

SELECT EXTRACT (? From Datetime_column) as variable_datetime_element from blog;