How is PHPPDO pre-processing cached?

We recently used pre-processing SQL (prepare) for the following two reasons ). High efficiency. Prevents SQL injection. After reading the official documentation, php.net data objects are still confused. Whether the efficiency will be improved or not. if preprocessing is the same as preprocessing SQL, just changing parameters, then I want to improve the efficiency... we recently used preprocessing SQL (prepare) for the following two reasons ).

1. High efficiency.

2. Prevents SQL injection.

Read the official documentation, php.net data object

However, it is still a bit confusing to use it.

Whether or not the efficiency will be improved. if the preprocessing is the same as the preprocessing SQL statement, but only the parameter change, I think the efficiency will be improved: However, if the following situations are involved:

// 1. the same pre-processing SQL statement cannot be used when the number of parameters is different. Two pre-processing statements are used as follows. SELECT name FROM product WHERE product_id in (?, ?, ?, ?); SELECT name FROM product WHERE product_id in (?, ?, ?, ?, ?); // PS: If the number of parameters is large, I don't know if the efficiency will be more direct (12, 34, 45, 23 ...) slow, but using id list directly cannot prevent SQL injection. // 2. if the same preprocessing is executed again after each preprocessing is completed, will it be faster. $ SQL = 'SELECT name FROM product where product_id =? '; $ Statement = PDO: prepare ($ preSql); $ product_id = 111; $ statement. bindParam (1, $ product_id); $ statement-> execute ();/* The most ideal case is to remove the annotation and directly use $ statement to call it twice. However, some common SQL operations are not so consistent (the same type of SQL may be executed separately), and it is not easy to analyze. $ Statement cannot be cached, and the same $ statement can be called. if the method has ended (the resource is released) after the first execution is completed, the same SQL statement with prepare can be re-executed for the second execution. At this time, I do not know whether the cached pre-processing SQL statement will be called. if it is cached in the DB, it should be possible to call it. However, if it is cached in PHP, I feel that it is necessary to re-process it again. in this way, I feel that it is highly efficient to directly use non-preprocessing SQL statements. * // $ SQL = 'SELECT name FROM product where product_id =? '; // $ Statement = PDO: prepare ($ preSql); $ product_id = 123; // $ statement. bindParam (1, $ product_id); $ statement-> execute ();/* Similarly, it is best to use parameters if the cache takes effect when processing ORDER. in this way, you do not need to re-process the SQL. It can also prevent SQL injection. */

I feel like I have written this. the most important thing is to know how PDO perpare is cached? Or there are similar documents (either in English ).

Reply content: pre-processing SQL (prepare) has been used recently for the following two reasons ).

1. High efficiency.

2. Prevents SQL injection.

Read the official documentation, php.net data object

However, it is still a bit confusing to use it.

Whether or not the efficiency will be improved. if the preprocessing is the same as the preprocessing SQL statement, but only the parameter change, I think the efficiency will be improved: However, if the following situations are involved:

// 1. the same pre-processing SQL statement cannot be used when the number of parameters is different. Two pre-processing statements are used as follows. SELECT name FROM product WHERE product_id in (?, ?, ?, ?); SELECT name FROM product WHERE product_id in (?, ?, ?, ?, ?); // PS: If the number of parameters is large, I don't know if the efficiency will be more direct (12, 34, 45, 23 ...) slow, but using id list directly cannot prevent SQL injection. // 2. if the same preprocessing is executed again after each preprocessing is completed, will it be faster. $ SQL = 'SELECT name FROM product where product_id =? '; $ Statement = PDO: prepare ($ preSql); $ product_id = 111; $ statement. bindParam (1, $ product_id); $ statement-> execute ();/* The most ideal case is to remove the annotation and directly use $ statement to call it twice. However, some common SQL operations are not so consistent (the same type of SQL may be executed separately), and it is not easy to analyze. $ Statement cannot be cached, and the same $ statement can be called. if the method has ended (the resource is released) after the first execution is completed, the same SQL statement with prepare can be re-executed for the second execution. At this time, I do not know whether the cached pre-processing SQL statement will be called. if it is cached in the DB, it should be possible to call it. However, if it is cached in PHP, I feel that it is necessary to re-process it again. in this way, I feel that it is highly efficient to directly use non-preprocessing SQL statements. * // $ SQL = 'SELECT name FROM product where product_id =? '; // $ Statement = PDO: prepare ($ preSql); $ product_id = 123; // $ statement. bindParam (1, $ product_id); $ statement-> execute ();/* Similarly, it is best to use parameters if the cache takes effect when processing ORDER. in this way, you do not need to re-process the SQL. It can also prevent SQL injection. */

I feel like I have written this. the most important thing is to know how PDO perpare is cached? Or there are similar documents (either in English ).

The main functions of the pre-processing statement are as follows:
1. more secure. PDO or underlying database libraries take special care of binding variables without constraints. if you always use preprocessing statements, you will not be vulnerable to SQL injection attacks.
2. sometimes the query may be faster. many databases cache the pre-processing statements in the query plan and use the symbols to prepare the statement in advance, instead of re-transmitting the entire query text. The most obvious thing is that you only need to declare a preprocessing object once, and then you can reuse the preprocessing statement object when encountering different variables.

Security is obviously more important in the two processes, which makes preprocessing statements indispensable. If efficiency is high, I think it is possible that the same object is used in multiple preprocessing, which improves efficiency.

Original Post