MySQL prepare statement performance bottom-neck

[howachen]

Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.

[Richard Levasseur]

If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail. com wrote:

Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.

[howachen]

Richard Levasseur 寫�：

If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail. com wrote:

Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.

Thanks...

So is that means in order to prevent SQL injection, we must need this
kind of overhead?

[Richard Levasseur]

ho******@gmail. com wrote:

Richard Levasseur 寫�：

If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail. com wrote:

Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.

Thanks...

So is that means in order to prevent SQL injection, we must need this
kind of overhead?

You could manually escape the values before you query, its just a lot
more work to $databaseHandle->escape($valu e) for every user submitted
value. This would most likely solve the performance problem.