How do I use PDO to query MySQL to avoid SQL injection risk? SQL injection Vulnerability analysis in thinkphp 3.1!

When we use the traditional mysql_connect, mysql_query method to connect query database, if the filter is not strict, there is the risk of SQL injection, resulting in the site is attacked, out of control. Although you can use the mysql_real_escape_string () function to filter user-submitted values, there are also flaws. The SQL injection risk can be avoided by using PHP's PDO extension's Prepare method .

PDO (PHP Data Object) is a significant feature of PHP5 's new addition, because before PHP 5, php4/php3 is a bunch of database extensions to connect to and process individual databases, such as Php_mysql.dll. The PHP5.6 will also be connected by default using PDO, and the MySQL extension will be used as a helper. Official: http://php.net/manual/zh/book.pdo.php

1. PDO Configuration

Before using the PDO extension, first enable this extension, php.ini, and remove the "Extension=php_pdo.dll" in front of the ";" To connect to the database, you also need to remove the ";" in front of the database extension associated with PDO. (typically Php_pdo_mysql.dll), then restart the Nginx server.

Extension=php_pdo.dllextension=php_pdo_mysql.dll

2. PDO connection to MySQL database

The default is not a long connection, to use a long database connection, you need to 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; Release

3. PDO Settings Properties

1), PDO has three kinds of error handling methods:

• Pdo::errmode_silent do not display error message, only set 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

When set to Pdo::errmode_silent, you can get an error message by calling ErrorCode () or errorinfo (), and of course in other cases.

2), because different databases have different case processing for returning field names, PDO provides pdo::attr_case settings (including Pdo::case_lower,pdo::case_natural,pdo::case_ UPPER) 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 value that the null value returned by the database corresponds to in PHP.

4. Pdo common method and its application

• Pdo::query () is primarily used for operations that have logged results returned, especially the select operation
• Pdo::exec () is primarily for operations 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, which can bind parameters and be powerful (preventing SQL injection)
• Pdo::lastinsertid () returns the last insert operation, the primary key column type is the final self-increment ID
• Pdostatement::fetch () is used to get a record
• Pdostatement::fetchall () is to get all recordsets to a collection
• Pdostatement::fetchcolumn () is a field that gets the result that specifies the first record, the default is the first field
• Pdostatement::rowcount (): Mainly for Pdo::query () and PDO::p Repare () The result set affected by the delete, INSERT, update operation, to Pdo::exec () Invalid method and select operation

5. PDO Operation MySQL Database instance

<?php$pdo = new PDO ("Mysql:host=localhost;dbname=db_demo", "Root", "" "), if ($pdo->exec (" INSERT into Db_demo (name, Content) (' title ', ' content ') ') {    echo "inserted 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); Numeric index array Form while ($row = $rs->fetch ()) {    print_r ($row);}? >view Source?<?phpforeach ($db->query ("SELECT * from Feeds") as $row) {    print_r ($row);}? >

How many rows of data are counted

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

Prepare Way

<?php$stmt = $dbh->prepare ("SELECT * from Test"), if ($stmt->execute ()) {while    ($row = $stmt->fetch ()) c1/>{        Print_r ($row);    }? >

Prepare parameterized Queries

<?php$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, real Real prepared statements is not used by default . To solve this problem, you must disable the emulation effect of prepared statements. Here's an example of creating a link using PDO:

<?PHP$DBH = new PDO (' Mysql:dbname=dbtest;host=127.0.0.1;charset=utf8 ', ' user ', ' Pass '), $DBH->setattribute (PDO :: Attr_emulate_prepares, false);? >

SetAttribute () This line is mandatory, it tells PDO to disable the impersonation preprocessing statement, and uses the 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 prohibited). Although you can set the properties of the character set in the config file (Charset=utf8), it is important to note that older versions of PHP (< 5.3.6) Ignore character parameters in DSN.

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

<?PHP$DBH = new PDO ("Mysql:host=localhost; Dbname=demo "," User "," pass "), $dbh->setattribute (Pdo::attr_emulate_prepares, false); Disables the emulation effect of the 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 above code will prevent SQL injection. Why is it?

When prepare () is called, the query statement has been sent to the database server with only placeholders at this time? Send in the past, no user submitted data, when called to execute (), the user submitted values will be sent to the database, they are separate transmission, the two independent, SQL attackers do not have a chance.

But here are a few things to keep in mind that PDO doesn't help you completely prevent SQL injection

1. You can't make a placeholder? Instead of a set of values, such as:

SELECT * FROM blog WHERE userid in (?);

2. You cannot have placeholders in place of data table or column names, such as:

SELECT * FROM blog ORDER by?;

3. You cannot allow placeholders to replace any other SQL syntax, such as:

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

How do I use PDO to query MySQL to avoid SQL injection risk? SQL injection Vulnerability analysis in thinkphp 3.1!