prepared queries recommendation

[Cardale]

How many queries should I limit myself to preparing?  Is there any overhead I should be worried about?  Do the prepared queries only stay "prepared" for one session or does it stay prepared as long as the server hasn't restarted?

[Mchl]

They stay prepared for one connection.

 

Here's some good read:

http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

[Cardale]

ahh...whats the point of that then?  Is there any way to have it stay prepared for all user sessions?  It is like loading program resources into memory much faster.

[Mchl]

You can create stored procedures/functions which pretty much have the same advantages.

 

The main point of prepared statements in web application is to avoid SQL injections. For desktop type application they can also bring some performance benefit.

[Cardale]

I see.  This prevents all forms of injection?

[Mchl]

If used properly it does.

[Daniel0]

You can create stored procedures/functions which pretty much have the same advantages.

 

Stored procedures don't protect you from SQL injections.

[Mchl]

How so? The SQL is precompiled so the queries can't be broken.

[Daniel0]

How so? The SQL is precompiled so the queries can't be broken.

 

You still have the problem of passing the procedure call to the database server along with the user input, and if you generate a query by concatenating strings in the stored procedure, you are back to the original problem. A quick search on Google and some MS SQL stuff: http://palisade.plynt.com/issues/2006Jun/injection-stored-procedures/

[Mchl]

Yeah that's true. But that's the problem with any dynamically created SQL, and you can't actually get rid of it (unless we get mysql_call() function sometime)

[Daniel0]

$stmt = $db->prepare('CALL getUser(?)');
$stmt->execute(array($_GET['username']));

 

[Mchl]

Hah, nifty