Thinkphp 3.1 How to use PDO query MySQL to avoid SQL injection

Introductory tutorials for PDO in PHP5





If you already know PDO, you can look directly at the following thinkphp 3.1 How to use PDO query MySQL to avoid SQL injection risk.





PDO (PHP Data Object) is a new PHP 5 things, in PHP 5.5, is strongly recommended to use PDO to process the database, will all the database extensions moved to the PECL, then the default is not our favorite php_ Mysql.dll and so on, then how to pinch, we only with the times, I have a small try a PDO.





"What's PDO?"





PDO is a major feature of PHP5 's new addition, because PHP4/PHP3 before PHP5 is a stack of database extensions to connect and process to individual databases, Php_mysql.dll, Php_pgsql.dll, Php_mssql.dll, Php_ Sqlite.dll and so on to connect MySQL, PostgreSQL, MS SQL Server, SQLite, we must, by virtue of ADOdb, PEAR::D B, Phplib: Database abstract classes such as:D B to help us, extremely cumbersome and inefficient , after all, how can the efficiency of the PHP code be higher than the extension slope that we write directly with C + +? Therefore, the emergence of PDO is inevitable, we have to calm the attitude of learning to accept the use, perhaps you will find that you can reduce a lot of kung fu Oh.








"Install PDO"





I am on the Windows XP SP2, so, the whole process is in the Windows line, as for Linux/freebsd platform, please find the data setup.





I'm PHP 5.4.31, already with the Php_pdo.dll extensions, but need a little setup to use.





Open C:\windows\php.ini, that's my PHP profile, and find the following line:





Extension_dir





This is the directory where we expand, and my PHP 5 extension is: C:\php5\ext, so I'll change the line to:





Extension_dir = "C:/php5/ext"





And then find it below the php.ini:





;;;;;;;;;;;;;;;;;;;;;;


; Dynamic Extensions;


;;;;;;;;;;;;;;;;;;;;;;





Here's a bunch of similar, extension=php_mbstring.dll stuff, and here's the PHP extension load configuration, and we're going to add our PDO extensions on the last side:





Extension=php_pdo.dll


Extension=php_pdo_mysql.dll


Extension=php_pdo_pgsql.dll


Extension=php_pdo_sqlite.dll


Extension=php_pdo_mssql.dll


Extension=php_pdo_odbc.dll


Extension=php_pdo_firebird.dll


; Extension=php_pdo_oci8.dll





A variety of PDO drivers, can be added to the full plus, but the back of the Php_pdo_oci8.dll, because I did not install the Oralce database, so without this, use a semicolon to annotate it. Then restart our web server, Iis/apache, my IIS, hey, table despise me, on windows, simple.





After restarting, write a phpinfo.php file in the document directory of our Web server, plus these:





<?php


Phpinfo ();


?>





Enter in the browser: http://localhost/phpinfo.php, if your page path is inconsistent, please enter it yourself.





The output of the content, if you can see smoothly:





Pdo


PDO Support Enabled


PDO drivers MySQL, Pgsql, SQLite, MSSQL, ODBC, Firebird





Well, congratulations on your installation success, otherwise please check the above steps carefully.











"A little test of a sledgehammer."





I use MySQL 5.5.25a, if you do not have MySQL installed, please install it yourself. We built MySQL and added table foo to the test library, including four fields such as Id,name,gender,time.





We started constructing the first PDO application, creating a pdo.php file in the Web document directory:

<?php $dsn  =  "Mysql:host=localhost;dbname=test"; $db  =
 new pdo ($dsn,  ' root ',  ' 123456 '); $count  =  $db->exec ("insert into foo set name =  ' Heiyeluren ', gender=
' Male ', Time=now ());
echo  $count; $db  = null; 

Don't understand what it means, let's talk slowly. This line:

$dsn = "Mysql:host=localhost;dbname=test";
The
is to construct our DSN (data source) to see the information included: the database type is MySQL, the host address is localhost, the database name is test, so a few messages. The data sources in different databases are constructed differently.

$db = new PDO ($dsn, ' root ', ' 123456 ');

Initializes a PDO object, and the constructor's arguments are the first of our data sources, the second is the user connecting to the database server, and the third parameter is the password. We can not guarantee the success of the connection, we will talk about the exception later, here we think it is connected successfully.

$count = $db->exec (INSERT into foo SET name = ' Heiyeluren ', gender= ' man ', Time=now ());
Echo $count;

Calls our successful PDO object to execute a query that inserts a record and uses the Pdo::exec () method to return a result that affects the record, so we output the result. Finally, you need to end the object resource:

$db = null;

The default is not a long connection, and if you need a database long connection, you need to add a final parameter: Array (pdo::attr_persistent => true):

$db = new PDO ($dsn, ' root ', ', ', Array (pdo::attr_persistent => true)); The

operation is so simple that it may not be much different from the previous one, which is similar to ADODB.

 
"continue to understand"

If we want to extract data, then we should use the Data acquisition feature. (The $db used below are the objects that are already connected above)

<?php
foreach ($db->query ("SELECT * from foo") as $row)
{
print_r ($row);
>

We can also use this method of obtaining:

<?php
$rs = $db->query ("SELECT * from foo");
while ($row = $rs->fetch ())
{
print_r ($row);
}
? >

If you want to get all the data in an array at once, you can do this:

<?php
$rs = $db->query ("SELECT * from foo");
$result _arr = $rs->fetchall ();
Print_r ($result _arr);
? >

Output:

Array (    [0] => array          (             [id] => 1              [0] => 1              [name] => heiyeluren              [1] => heiyeluren              [gender] =>  male              [2] =>  male              [time] => 2006-10-28 23:14:23              [3] => 2006-10-28 23:14:23          )}

We look inside the records, the digital index and the associated index all have, waste resources, we only need to associate the index:

<?php
$db->setattribute (pdo::attr_case, pdo::case_upper);
$rs = $db->query ("SELECT * from foo");
$rs->setfetchmode (PDO::FETCH_ASSOC);
$result _arr = $rs->fetchall ();
Print_r ($result _arr);
? >

Look at the code above, the SetAttribute () method is to set some properties, the main properties are: Pdo::attr_case, Pdo::attr_errmode, and so on, we need to set up here is Pdo::attr_case, When we use the associated index to get the dataset, the associated index is uppercase or lowercase, and there are several options:

Pdo::case_lower--Forcing the column name to be lowercase
Pdo::case_natural--column names in the original way
Pdo::case_upper--Force column name to uppercase

We use the Setfetchmode method to set the type to get the return value of the result set, as well as the same type:

PDO::FETCH_ASSOC--Associative array form
Pdo::fetch_num--Digital indexed array form
Pdo::fetch_both--both array forms are available, which is the default
Pdo::fetch_obj-Similar to previous mysql_fetch_object () in the form of objects

Of course, in general we are using PDO::FETCH_ASSOC, specifically using what, according to your own needs, other get type reference manuals.

In addition to this way of getting the data, there is this:

<?php
$rs = $db->prepare ("SELECT * from foo");
$rs->execute ();
while ($row = $rs->fetch ())
{
print_r ($row);
}
? >

Actually, it's almost. If you want to obtain a field result in a specified record, you can use Pdostatement::fetchcolumn ():

<?php
$rs = $db->query ("SELECT COUNT (*) from foo");
$col = $rs->fetchcolumn ();
echo $col;
? >

It is generally good to use Fetchcolumn () for Count statistics or some records that require only a single field.











simply summarize the above operation





Query operations are mainly Pdo::query (), Pdo::exec (), PDO::p repare (). Pdo::query () is used primarily for operations that have record results returned, especially select operations, where the pdo::exec () is primarily for operations returned without a result set, such as INSERT, UPDATE, delete, which returns the number of columns affected by the current operation. PDO::p repare () is mainly preprocessing operations, the need to $rs->execute () to perform preprocessing of the SQL statements, this method can be binding parameters, powerful, not this article can be simple to say, you can refer to the manual and other documents.





Get result set operations mainly: Pdostatement::fetchcolumn (), Pdostatement::fetch (), Pdostatement::fetchall (). Pdostatement::fetchcolumn () is a field that gets the result to specify the first record, and the default is the first field. Pdostatement::fetch () is used to get a record, Pdostatement::fetchall () is to get all the recordset into one, and the results can be obtained through Pdostatement:: Setfetchmode to set the type that requires the result collection.





In addition there are two peripheral operations, one is Pdo::lastinsertid () and Pdostatement::rowcount (). Pdo::lastinsertid () is the last self-increasing ID that returns the last insert operation, and the primary key column type is the increment. Pdostatement::rowcount () is primarily used 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.











"Error Handling"





What if you encounter errors in your program? Here we describe the error message and exception handling for the PDO class.





1. Object-oriented approach





Let's take a look at the handling of connection errors and so on, using object-oriented methods:

<?php
try
{
$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);
$DB = null;
}
catch (pdoexception $e)
{
print ' Error: '. $e->getmessage ().    "<br/>";
Die ();
>

Here we use our PHP 5 object-oriented exception handling feature to initialize an exception class if there is an exception in the initialization call pdoexception.

Attribute structure of Pdoexception exception class

<?php
class Pdoexception extends Exception
{public
$errorInfo = null;            Error message, you can call Pdo::errorinfo () or Pdostatement::errorinfo () to access the
protected $message;            Exception information, you can try Exception::getmessage () to access
protected $code; SQL Status error code, you can use Exception::getcode () to access the
}
?>

This exception handling class is the integrated PHP 5 built-in exception handling class, we simply look at the PHP 5 built-in exception handling class structure:

<?php class exception {    //  Properties     protected  $message  =  ' unknown exception ';   //  exception information     protected $ code = 0;                         //  User Custom Exception code     protected  $file;                             //  filename of exception occurred      protected  $line;                             //  line number of the exception that occurred      //  method     final function getmessage ();                 //  Return exception Information     final  Function getcode ();                    //  return Exception Code     final function getfile ();                    //   Returns the file name of the exception that occurred     final function getline ();                    //  returns the line number of the code where the exception occurred      final function gettrace ();                   // backtrace ()   array     final  function gettraceasstring ();          //   gettrac that has been formed into a stringE ()   info}?> 

Accordingly, in the code can be appropriate to call GetFile () and getline () for error location, more convenient for debugging.

2, the use of process-oriented approach

First look at the code:

<?php
$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);
$rs = $db->query ("Select AA,BB,CC from foo");
if ($db->errorcode ()!= ' 00000 ')
{
Print_r ($db->errorinfo ());
Exit;
}
$arr = $rs->fetchall ();
Print_r ($arr);
$DB = null;
? >

The PDO and Pdostatement objects have errorcode () and ErrorInfo () methods, and if there are no errors, errorcode () returns 00000, or it returns some error codes. ErrorInfo () returns an array that includes PHP-defined error codes and MySQL error codes and error messages, and the array structure is as follows:





Array


(


[0] => 42s22


[1] => 1054


[2] => Unknown column ' aaa ' in ' Field list '


)





The results of errorcode () are up to date every time the query is executed, so we can easily control the error message display.











"Simple Summary"





From the use above, I see that the PDO function is really powerful, and there are some things I did not talk about, such as binding parameters, preprocessing, stored procedures, transactions, and so on. There are different data to expand the DSN structure, the Oracle database itself a lot of special things, need to go deep to learn to understand, this article is just a simple description of some introductory knowledge, is a simple understanding of PDO.





"Transactions and Autocommit"





Now that you have connected to MySQL via PDO, you should understand how PDO manages the transaction before issuing the query. If you have not previously contacted a transaction, you first need to know the 4 characteristics of the transaction: atomicity (atomicity), consistency (consistency), independence (isolation) and persistence (durability), or ACID. In layman's words, for any work performed in a transaction, even if it is staged in stages, it is guaranteed that the work will be securely applied to the database and will not be affected by other connections when the work is committed. Transactional work can be automatically revoked on request (assuming you haven't committed it yet), which makes error handling in the script easier.





A transaction is usually achieved by saving a batch of changes and taking them into effect. The advantage of this is that the efficiency of these updates can be greatly improved. In other words, transactions can make scripts faster and may be more robust (although it is necessary to use transactions correctly to gain such benefits).





Unfortunately, not every database supports transactions (MYSQL5 support transactions, Mysql4 I don't know), so when you first open a connection, PDO needs to run in the so-called autocommit (auto-commit) mode. Autocommit mode means that if the database supports transactions, every query you run has its own implicit transaction, and if the database does not support transactions, there is no such transaction for each query. If you need a transaction, you must use the Pdo::begintransaction () method to start a transaction. If the underlying driver does not support transactions, a pdoexception will be thrown (regardless of the error handling setting: This is always a critical error state). In a transaction, you can use Pdo::commit () or pdo::rollback () to end the transaction, depending on whether the code that is running in the transaction succeeds.





When the script ends, or when a connection is about to be closed, if there is an unfinished transaction, then PDO automatically rolls back the transaction. This is a security measure that helps to avoid inconsistencies when the script ends abnormally-if the transaction is not explicitly committed, then a rollback is performed to ensure the security of the data, assuming there is an inconsistency somewhere.

<?php//  example from http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0505furlong/index.html try {     $db  = new pdo (' odbc:sample ',  ' db2inst1 ',  ' ibmdb2 '), 
Array (pdo_attr_persistent => true));
    echo  "connected\n";
     $db->setattribute (pdo_attr_errmode, pdo_errmode_exception);
     $db->begintransaction ();      $db->exec ("insert into staff  (id, first, last)  values
  (23,  ' Joe ',  ' Bloggs '));      $db->exec ("insert into salarychange " (id, amount, 
ChangeDate)  values  (23, 50000, now ()) ");
     $db->commit (); } catch  (exception  $e) {     $db->rollback ()     echo  "
failed:  " .  $e->getmessage (); }?>

In the example above, suppose we create a set of entries for a new employee that has an ID number, that is, 23. In addition to entering this person's basic data, we also need to record the employee's salary. Two updates are simple to complete, but by including both updates in the BeginTransaction () and commit () calls, you can guarantee that the changes will not be visible to others until the changes are complete. If an error occurs, the catch block can roll back any changes that have occurred since the transaction started and print out an error message.

does not necessarily make updates in a transaction. You can also send complex queries to extract data, and you can use that information to build more updates and queries. When a transaction is active, you can ensure that other people cannot make changes while the work is in progress. In fact, this is not 100% correct, but if you have not heard of the transaction before, this introduction is also not possible.

 

preprocessing statements and stored procedures

Many more mature databases support the concept of preprocessing statements. What is a preprocessing statement? You can think of a preprocessing statement as a compiled template for the SQL you want to run, which can be customized using variable parameters. There are two great benefits to preprocessing statements:

    queries only need to parse (or prepare) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. For complex queries, this process takes a long time, and if you need to repeat the same query multiple times with different parameters, the process will greatly reduce the speed of your application. By using preprocessing statements, you can avoid repeating analysis/compilation/optimization cycles. In short, a preprocessing statement uses fewer resources and thus runs faster.
    parameters provided to preprocessing statements do not need to be enclosed in quotation marks, and the driver handles these. If your application uses preprocessing statements exclusively, you can ensure that no SQL intrusion occurs. (However, there is still a risk if you still build other parts of the query on untrusted input.) The

Preprocessing statement is so useful that PDO actually breaks the rule set in Goal 4: If the driver does not support preprocessing statements, then PDO will emulate the preprocessing statements.

Instance: PDO application Example:

<?php $dbms  =  ' mysql ';        //  database type  oracle   with ODI, for developers, using a different database, just change this, do not have to remember so many functions $host  =  ' localhost ';    //  Database host name $dbName  =  ' test ';        //  database used $user  =   ' root ';            //  database connection user name $pass  =  ';               
 //  corresponding password $dsn  =  "$dbms: host= $host;d bname= $dbName"; Class db extends pdo {    public function __construct ()      {        try          {            parent::__construct ("$
GLOBALS[DSN] ",  $GLOBALS [' User '],  $GLOBALS [' Pass ']); &nbsP;      &NBSP}         catch  ( pdoexception  $e)         {             die ("error: "  .  $e->__tostring ()  .  "<br/
> ");         }          public  Final function query ($sql)     {         Try         {         
   return parent::query ($this->setstring ($sql));        &NBSP}         catch  ( pdoexception  $e)         {             die ("Error:&nbSP; "
 .  $e->__tostring ()  .  "<br/>");         }     }     private  final function setstring ($sql)     {      
  echo  "I want to deal with $sql";
        return  $sql;
   &NBSP}} $db  = newdb ();
$db->setattribute (Pdo::attr_case, pdo::case_upper); foreach  ($db->query (' Select * from xxxx_menu ')  as  $row) {   
 print_r ($row); $db->exec (' delete from   ' xxxx_menu '  where mid=43 ');?>

Thinkphp 3.1 How to use PDO to query MySQL to avoid SQL injection risk

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. PHP5.6 will also default to the use of PDO connection, MySQL extension will be used as a secondary. Official: http://php.net/manual/zh/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 Nginx server.





Extension=php_pdo.dll


Extension=php_pdo_mysql.dll





2, PDO connection MySQL database





$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:





$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





$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) PDO provides pdo::attr_case settings items (including pdo::case_lower,pdo::case_natural,pdo::case_upper) because different databases have different case-handling of 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

<?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 succeeded!"    ";
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);
>
<?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 Way

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

Prepare parameterized Query

<?php
$stmt = $dbh->prepare ("SELECT * from test where name =?");
if ($stmt->execute (Array ("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:

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

The

SetAttribute () line 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 look at a complete example of code usage:

<?php $dbh  = new pdo ("Mysql:host=localhost; dbname=
Demo ", " "User",  "pass"); Emulation effect of disabling prepared statements $dbh->setattribute (pdo::attr_emulate_prepares, false); //  $
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);     &NBSP} $dbh  = null; 

The code above will prevent 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 user submitted values will be sent to the database, they are transmitted separately, the two independent, The SQL attacker did not have a chance.

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

1, you can't have placeholders? Replace a set of values, such as:

    select * from blog WHERE userid in (?);

2, you cannot have placeholders instead of data table or column names, such as:

    select * from blog order by?;

3, you cannot let placeholders replace any other SQL syntax, such as

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