Shulou(Shulou.com)06/01 Report--

The following content mainly brings you PDO query mysql how to avoid SQL injection, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

When using traditional mysql_connect and mysql_query methods to connect to the query database, if the filtering is not strict, there is a risk of SQL injection. Although it is possible to use the mysql_real_escape_string () function to filter the values submitted by the user, it is also flawed. The sql injection risk can be avoided by using the prepare method extended by PHP's PDO.

PDO (PHP Data Object) is a major addition to PHP5, because before PHP5, php4/php3 was a bunch of database extensions to connect and process with various databases, such as php_mysql.dll. The PDO connection will also be used by default in PHP6, and the mysql extension will be used as an auxiliary. Official address: http://php.net/manual/en/book.pdo.php

1. PDO configuration

Before using the PDO extension, you need to enable this extension. In php.ini, remove the ";" sign before "extension=php_pdo.dll". To connect to the database, you also need to remove the ";" sign before the PDO-related database extension (usually using php_pdo_mysql.dll), and then restart the Apache CVM.

Extension=php_pdo.dll extension=php_pdo_mysql.dll

2. PDO connects to mysql database

$dbh = new PDO ("mysql:host=localhost;dbname=mydb", "root", "password")

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

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

3. PDO setting properties

PDO has three ways to handle errors:

PDO::ERrmODE_SILENT does not display error messages, only sets the error code

PDO::ERrmODE_WARNING display warning error

PDO::ERrmODE_EXCEPTION throws exception

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

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

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

Specify the numeric 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 methods of PDO and their applications

PDO::query () is mainly used for operations that return recorded results, especially SELECT operations.

PDO::exec () is mainly for operations that are not returned by the result set, such as INSERT, UPDATE, etc.

PDO::prepare () is mainly a preprocessing operation, and the SQL statement in the preprocessing needs to be executed through $rs- > execute (). This method can bind parameters and is more powerful (this is how to prevent sql injection)

PDO::lastInsertId () returns the last insert operation, and the primary key column type is the last self-incrementing ID

PDOStatement::fetch () is used to get a record

PDOStatement::fetchAll () is to get all recordsets into one set

PDOStatement::fetchColumn () is a field that specifies the first record to get the result. The default is the first field.

PDOStatement::rowCount (): the result set that is mainly used for PDO::query () and PDO::prepare () to perform DELETE, INSERT, and UPDATE operations, and is not valid for PDO::exec () methods and SELECT operations.

5.PDO operates MYSQL database instance

The line setAttribute (), which is mandatory, tells PDO to disable the analog 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 disabled).

Although you can set the charset=utf8 of the character set in the configuration file, it is important to note that older versions of PHP (< 5.3.6) ignored character parameters in DSN.

Complete code usage example:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.