Let it be...顺其自然还是放任自流

为什么要选择预处理语句？Why prepared statements?

                                      

上次在GreenAMP Standard 050109推出的时候，我说建议大家使用MySQL 4.1系列，因为它支持Prepared Statement（预处理语句），这是防止SQL Injection攻击最有效的手段，也会大幅提高复杂SQL的多次执行效率。。。（全文在这里）

今天看到MySQL官方网站上的说法：

Why use prepared statements?

There are numerous advantages to using prepared statements in your applications, both for security and performance reasons.

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times, such as when doing many INSERT statements.

(Note: While it will not happen with MySQL 4.1, future versions will also cache the execution plan for prepared statements, eliminating another bit of overhead you currently pay for each query execution.)

The second place where performance may increase is through the use of the new binary protocol that prepared statements can use. The traditional protocol in MySQL always converts everything into strings before sending them across the network. This means that the client converts the data into strings, which are often larger than the original data, sends it over the network (or other transport) to the server, which finally decodes the string into the correct datatype. The binary protocol removes this conversion overhead. All types are sent in a native binary form, which saves the conversion CPU usage, and can also cut down on network usage.

原文：http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

哈哈，提到的两点理由（both for security and performance reasons）和我的说法如出一辙。

MySQL.com认为，Prepared Statements一方面可以提高安全性，另一方面可以提升性能。文章我就不一一翻译了，感兴趣的读者自己看看原文吧，^_^

【作者: 覃健祥】【访问统计:】【2005年01月18日 星期二 16:25】【 加入博采】【打印】