Php_pdo pre-processing statement, php_pdo pre-processing statement

Php_pdo pre-processing statement, php_pdo pre-processing statement

This article mainly introduces the php_pdo preprocessing statement. Let's take a look at the details below.

I. Preprocessing statements can bring two benefits:

1. the query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database will analyze, compile, and optimize
Plan to execute the query. For complex queries, this process takes a long time. If you need to repeat the same query multiple times with different parameters, this process will increase
Greatly speed down applications. By using preprocessing statements, you can avoid repeated analysis, compilation, and optimization cycles. In short, preprocessing statements consume less resources because
And runs faster.

2. parameters provided to the pre-processing statement do not need to be enclosed in quotation marks, and the driver will automatically process them. If the application only uses preprocessing statements, you can ensure that
SQL Injection occurs. (However, if the other part of the query is constructed by unescaped input, there is still a risk of SQL injection ).

Ii. Preprocessing example:

<? Php //? There are three binding methods for prefetch statements in expression. // 1. connect to the database try {$ pdo = new PDO ("mysql: host = localhost; dbname = jikexueyuan", "root", "");} catch (PDOException $ e) {die ("database connection failed ". $ e-> getMessage ();} // 2. pre-processing SQL statement $ SQL = "insert into stu (id, name, sex, age) values (?,?,?,?) "; $ Stmt = $ pdo-> prepare ($ SQL); // 3. Right? BindValue (1, null); $ stmt-> bindValue (2, 'test55 '); $ stmt-> bindValue (3, 'w'); $ stmt-> bindValue (4, 22); * // bindValue method 2/* $ stmt-> bindParam (1, $ id); $ stmt-> bindParam (2, $ name); $ stmt-> bindParam (3, $ sex); $ stmt-> bindParam (4, $ age ); $ id = null; $ name = "test66"; $ sex = "m"; $ age = 33; * // The third binding method // $ stmt-> execute (array (null, 'test777', '22', 55); // 4. run $ stmt-> execute (array (null, 'test77', '22', 55); echo $ stmt-> rowCount ();

<? Php // alias expression preprocessing statement three binding methods // 1. connect to the database try {$ pdo = new PDO ("mysql: host = localhost; dbname = jikexueyuan", "root", "");} catch (PDOException $ e) {die ("database connection failed ". $ e-> getMessage ();} // 2. pre-processed SQL statement $ SQL = "insert into stu (id, name, sex, age) values (: id,: name,: sex,: age )"; $ stmt = $ pdo-> prepare ($ SQL); // 3. right? Parameter binding // (first binding method)/* $ stmt-> bindValue ("id", null); $ stmt-> bindValue ("name ", 'ceshi1'); $ stmt-> bindValue ("sex", 'w'); $ stmt-> bindValue ("age", 22 ); * // bindParam ("id", $ id); $ stmt-> bindParam ("name", $ name ); $ stmt-> bindParam ("sex", $ sex); $ stmt-> bindParam ("age", $ age); $ id = null; $ name = "ceshi2 "; $ sex = "m"; $ age = 33; * // The third binding method // $ stmt-> execute (array (null, 'test777', '22 ', 55); // 4. run $ stmt-> execute (array ("id" => null, "name" => "ceshi3", "sex" => "w ", "age" => 66); echo $ stmt-> rowCount ();

<? Php // use pre-processing SQL to execute queries and bind results to output // 1. connect to the database try {$ pdo = new PDO ("mysql: host = localhost; dbname = jikexueyuan", "root", "");} catch (PDOException $ e) {die ("database connection failed ". $ e-> getMessage ();} // 2. pre-processed SQL statement $ SQL = "select id, name, sex, age from stu"; $ stmt = $ pdo-> prepare ($ SQL); // 3. run $ stmt-> execute (); $ stmt-> bindColumn (1, $ id); $ stmt-> bindColumn (2, $ name ); $ stmt-> bindColumn ("sex", $ sex); $ stmt-> bindColumn ("age", $ age ); while ($ row = $ stmt-> fetch (PDO: FETCH_COLUMN) {echo "{$ id }:{$ name }:{$ sex }: {$ age} <br> ";}/ * foreach ($ stmt as $ row) {echo $ row ['id']. "--------". $ row ['name']. "<br> ";}*/

Best Practice:

// 1. connect to the database try {$ pdo = new PDO ("mysql: host = localhost; dbname = jikexueyuan", "root", "");} catch (PDOException $ e) {die ("database connection failed ". $ e-> getMessage ();} // 2. pre-processed SQL statement $ SQL = 'select catid, catname, catdir from cy_category where parentid =: parentid'; $ stmt = $ pdo-> prepare ($ SQL ); $ params = array ('parentid' => $ subcatid); $ stmt-> execute ($ params); // $ row = $ stm-> fetchAll (PDO :: FETCH_ASSOC); while ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC) {var_dump ($ row); echo "<br> ";}

Preprocessing batch operation instances:

<? Php // use a pre-processing statement for repeated INSERT // The following example uses name and value to replace the corresponding name placeholder to execute an INSERT query $ stmt = $ dbh-> prepare ("INSERT REGISTRY (name, value) VALUES (: name,: value) "); $ stmt-> bindParam (': name', $ name); $ stmt-> bindParam (': value ', $ value); // insert a row $ name = 'one'; $ value = 1; $ stmt-> execute (); // insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute (); // use a pre-processing statement to insert data repeatedly. // The following example replaces it with name and value? To execute an insert query. $ Stmt = $ dbh-> prepare ("insert into registry (name, value) VALUES (?, ?) "); $ Stmt-> bindParam (1, $ name); $ stmt-> bindParam (2, $ value); // insert a row $ name = 'one '; $ value = 1; $ stmt-> execute (); // insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute (); // use a pre-processing statement to obtain data. // The following example shows how to obtain data based on the format provided by the key value. User input is automatically enclosed in quotation marks, so there is no risk of SQL injection attacks. $ Stmt = $ dbh-> prepare ("SELECT * from registry where name =? "); If ($ stmt-> execute (array ($ _ GET ['name']) {while ($ row = $ stmt-> fetch ()) {print_r ($ row) ;}}?>

Summary

The above is all the content of this article. I hope the content of this article will be helpful for everyone to learn or use php. If you have any questions, please leave a message.