Use PDO in php to query Mysql to avoid SQL Injection examples and precautions

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,PDOConfiguration
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 "before" extension of the PDO-related database "; "(php_pdo_mysql.dll is generally used), and then restart the Apache server.
 
Extension = php_pdo.dll
Extension = php_pdo_mysql.dll
2,PDOConnectionMysqlDatabase
$ Dbh = new PDO ("mysql: host = localhost; dbname = db_demo", "root", "password"); by default, it is not a persistent connection. To use a persistent 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; // (Release)
 
3,PDOSet attributes
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.
$ Db-> setAttribute (PDO: ATTR_ERrmODE, PDO: ERrmODE_EXCEPTION); when set to PDO: ERrmODE_SILENT, you can call errorCode () or errorInfo () to obtain error information, of course, 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,PDOCommon Methods and 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,PDOOperationMYSQLDatabase instance
 
<? 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 ();
}
?>
 
<? 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 );
}
?>
<? Php
Foreach ($ 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 Method
 
$ Stmt = $ dbh-> prepare ("select * from test ");
If ($ stmt-> execute ()){
While ($ row = $ stmt-> fetch ()){
Print_r ($ row );
}
}
 
Prepare parameterized Query
$ 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:
$ 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:
$ 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:
 
SELECT * FROM blog WHERE userid IN (? );
2. You cannot replace a placeholder with a data table name or column name, for example:
SELECT * FROM blog order ?;
3. Can't you make placeholders? Replace any other SQL syntax, such:
 
Select extract (? FROM datetime_column) AS variable_datetime_element FROM blog;