Prepared statements

MattF · 2010-01-19 14:17:23

Opinion time. If designing something from scratch, would you stick with just using sql_escaped strings or go down the prepared statement avenue too? Does preparing the statements introduce a notable overhead?

Franz · 2010-01-19 14:25:33

No overhead that I know of. If you can, go for prepared statements. They are much safer, and you can't even forget about the security precaution if you use them...

MattF · 2010-01-19 14:30:37

Cheers. I'll get some revision done on those then. I've very few queries in existence at the moment, so it's the prime time to add them. Is it best, (as an extra level of caution), to still escape strings if using prepared statements, or should that habit be dropped when using prepared statements?

Franz · 2010-01-19 14:39:15

That would result in you having escaped strings in the database.

If I understood prepared statements correctly, the strings or whatever parameters you hand over are sent separated from the actual query to the database, and only there they are used to replace the placeholders - after parsing the query.

Actually, an interesting question that brings up for me: Is it possible to use the placeholder in combination with some arithmetic operation - something like INSERT INTO foo (bar) VALUES (?+2). This is obviously a pointless example, but a more realistic one could be adding the result of some SELECT subquery.

MattF · 2010-01-19 14:49:19

lie2815 wrote:

If I understood prepared statements correctly, the strings or whatever parameters you hand over are sent separated from the actual query to the database, and only there they are used to replace the placeholders - after parsing the query.

That makes things a bit clearer. I'd not figured the way they worked yet. So literally the values just get dumped in the DB wherever the respective placeholders are, and no processing is done with the strings as such in any other regard.

Actually, an interesting question that brings up for me: Is it possible to use the placeholder in combination with some arithmetic operation - something like INSERT INTO foo (bar) VALUES (?+2). This is obviously a pointless example, but a more realistic one could be adding the result of some SELECT subquery.

I'll let you know what I find out on that one. I get the feeling I've got a good few hours of reading in front of me, switching over to these, so I'll quite likely find the answer to that scenario.

MattF · 2010-01-19 17:29:00

Just been playing around with the code, and the likes of what you mention shouldn't be a problem, due to the way the queries/statements are written. Still not 100 percent certain yet, but I think it should be straight forward enough. It's taking some getting used to though. This is the basic code I've been running just to test the layer.

$db->prepare('dbtest', 'SELECT email FROM users WHERE username=$1 AND username!=$2');
$db->execute('dbtest', array('Spider', 'Guest'));
$info = $db->result();

Should be simple enough to adapt that example to allow and mix and match of sub queries, etc. Just spent ages trying to figure out why things weren't working, btw. Eventually realised that I'd forgotten to assign the result resource in the DB layer for the execute. The simplest of things...

Last edited by MattF (2010-01-19 20:07:41)

MattF · 2010-01-20 00:35:34

Must say, one other notable benefit of this is that it does make the code look so much cleaner and easier to read too. It's not half as bad to read through and check for syntax. Plus, using query_params on the simple statements makes it a fairly straight forward enough replacement for query.

Forums

#1 2010-01-19 14:17:23

Prepared statements

#2 2010-01-19 14:25:33

Re: Prepared statements

#3 2010-01-19 14:30:37

Re: Prepared statements

#4 2010-01-19 14:39:15

Re: Prepared statements

#5 2010-01-19 14:49:19

Re: Prepared statements

#6 2010-01-19 17:29:00

Re: Prepared statements

#7 2010-01-20 00:35:34

Re: Prepared statements

Board footer