How to Use PDO to query Mysql in Php to avoid SQL Injection risks

When we use the traditional mysql_connect and mysql_query methods to connect to the query database, if the filtering is lax, there is a risk of SQL injection, resulting in the website being attacked and out of control. Although the mysql_real_escape_string () function can be used to filter user submitted values, there are also defects. The prepare method extended by PDO in PHP can avoid the risk of SQL injection.

PDO (PHP Data Object) is a major new feature of PHP5, because php4/php3 before PHP 5 was a bunch of database extensions to connect to and process various databases, for example, php_mysql.dll. PHP6 will also use the PDO connection by default, and mysql expansion will be used as an aid. Http://php.net/manual/en/book.pdo.php

1. PDO Configuration
Before using the PDO extension, you must first enable this extension, PHP. in ini, remove the ";" sign before "extension = php_pdo.dll"; ". To connect to the database, remove the database extension before "; "(php_pdo_mysql.dll is generally used), and then restart the Apache server.

Copy codeThe Code is as follows: extension = php_pdo.dll
Extension = php_pdo_mysql.dll

2. PDO connects to the mysql database Copy codeThe Code is as follows: $ dbh = new PDO ("mysql: host = localhost; dbname = db_demo", "root", "password ");

The default value is not persistent connection. To use persistent connection, add the following parameters at the end:Copy codeThe Code is as follows: $ dbh = new PDO ("mysql: host = localhost; dbname = db_demo", "root", "password", "array (PDO: ATTR_PERSISTENT => true) ");
$ Dbh = null; // (Release)

3. Set Properties for PDO

1) There are three error handling methods for PDO:

• PDO: ERrmODE_SILENT does not display error messages, but only sets error codes.
• PDO: ERrmODE_WARNING: a warning error is displayed.
• PDO: ERrmODE_EXCEPTION throws an exception

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

Copy codeThe Code is as follows: $ db-> setAttribute (PDO: ATTR_ERrmODE, PDO: ERrmODE_EXCEPTION );

When set to PDO: ERrmODE_SILENT, you can call errorCode () or errorInfo () to obtain the error information. Of course, you can also obtain the error information in other cases.

2) because different databases process different names of returned fields in different cases, PDO provides PDO: ATTR_CASE settings (including PDO: CASE_LOWER, PDO: CASE_NATURAL, PDO :: CASE_UPPER) to determine the case sensitivity of the returned field name.

3) specify the value of the NULL value returned by the database in php 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 operations that return records, especially SELECT operations.
PDO: exec () is mainly used for operations that do not return result sets, such as INSERT and UPDATE operations.
PDO: prepare () is mainly a pre-processing operation. You need to run the SQL statement in the pre-processing through $ rs-> execute (). This method can bind a parameter, powerful functions (this is the only option to prevent SQL injection)
PDO: lastInsertId () returns the last insert operation. The primary key column type is the last auto-increment ID of auto-increment.
PDOStatement: fetch () is used to obtain a record.
PDOStatement: fetchAll () is used to obtain all record sets to a set.
PDOStatement: fetchColumn () is a field in the first record specified in the result. The default value is the first field.
PDOStatement: rowCount (): Mainly used for the results set affected by the DELETE, INSERT, and UPDATE operations on PDO: query () and PDO: prepare (). For PDO :: the exec () method and SELECT operation are invalid.

5. PDO for MYSQL database instances

Copy codeThe Code is 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 "inserted successfully! ";
Echo $ pdo-> lastinsertid ();
}
?>

Copy codeThe Code is as follows: <? Php
$ Pdo = new PDO ("mysql: host = localhost; dbname = db_demo", "root ","");
$ Rs = $ pdo-> query ("select * from test ");
$ Rs-> setFetchMode (PDO: FETCH_ASSOC); // associate an array
// $ Rs-> setFetchMode (PDO: FETCH_NUM); // number index array format
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>

Copy codeThe Code is as follows: <? Php
Foreach ($ db-> query ("SELECT * FROM feeds") as $ row)
{
Print_r ($ row );
}
?>

How many rows of data are countedCopy codeThe Code is as follows: $ SQL = "select count (*) from test ";
$ Num = $ dbh-> query ($ SQL)-> fetchColumn ();

Prepare MethodCopy codeThe Code is as follows: $ stmt = $ dbh-> prepare ("select * from test ");
If ($ stmt-> execute ()){
While ($ row = $ stmt-> fetch ()){
Print_r ($ row );
}
}

Prepare parameterized QueryCopy codeThe Code is as follows: $ stmt = $ dbh-> prepare ("select * from test where name =? ");
If ($ stmt-> execute (array ("david "))){
While ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )){
Print_r ($ row );
}
}

[The following describes how to prevent SQL injection]

Real prepared statements is not used by default when PDO is used to access the MySQL database. To solve this problem, you must disable the simulation effect of prepared statements. The following is an example of using PDO to create a link:

Copy codeThe Code is as follows: $ dbh = new PDO ('mysql: dbname = dbtest; host = 127.0.0.1; charset = utf8', 'user', 'pass ');
$ Dbh-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );

SetAttribute () is mandatory. It instructs PDO to disable simulated preprocessing statements and uses real parepared statements. This ensures that SQL statements and their values are not parsed by PHP before being passed to the mysql server (all possible malicious SQL injection attacks are prohibited ). Although you can set the character set attribute (charset = utf8) in the configuration file, you must note that the earlier version of PHP (<5.3.6) ignores the character parameter in DSN.

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

Copy codeThe Code is as follows: $ dbh = new PDO ("mysql: host = localhost; dbname = demo", "user", "pass ");
$ Dbh-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false); // disable the simulation 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 above code prevents SQL injection. Why?

When prepare () is called, the query statement has been sent to the database server, and there is only a placeholder? The data submitted by the user is not sent in the past. When the call is to execute (), the value submitted by the user is sent to the database. They are sent separately, and the two are independent, SQL attackers have no chance.

But we need to pay attention to the following situations. PDO cannot help you prevent SQL injection.

1. You cannot make placeholders? Replace a group of values, such:

Copy codeThe Code is as follows: SELECT * FROM blog WHERE userid IN (? );

2. You cannot replace a placeholder with a data table name or column name, for example:Copy codeThe Code is as follows: SELECT * FROM blog order ?;

3. Can't you make placeholders? Replace any other SQL syntax, such:Copy codeThe Code is as follows: select extract (? FROM datetime_column) AS variable_datetime_element FROM blog;