Re: [PHP-DB] String Parsing/Escaping
hi Alexander, interesting question regarding 'safety' v. readability v. speed - I'm sure you'll get different views depending on who you ask. Here is my take: Alexander Mueller wrote: Hi, below are three versions of an SQL call along with escaping the passed value. $value=mysql_escape_string($_POST['value']); mysql_query('SELECT * FROM table WHERE field='.$value.''); + Fastest Code - Con: Bad Readability, Value needs to be escaped separately I rate speed as the least important issue - you can alway use a faster machine, get some more RAM etc if you really need a quick speed fix. making the code faster is the last exercise I do in any given project, and I almost always choose readability/'safety' over speed $value=mysql_escape_string($_POST['value']); mysql_query(sprintf('SELECT * FROM table WHERE field=%s', $value)); + Good Readability - Value needs to be escaped separately This is a compromise - can't imagine why anyone would choose this one. It's not my choice, I'll just skip to number 3 :-) sql_sprintf() is a custom version of sprintf() which automatically escapes all passed parameters. mysql_query(sql_sprintf('SELECT * FROM table WHERE field=%s', $_POST['value'])); + Good Readability, Value does not need to be escaped separately - Slowest Code YEAH! indeed its the slowest. but its so much more readable and you know its alot more maintainable (you don't have to change the escaping, sprintf'ing strategy in 100 places.). Its safer too because there is no chance of forgetting to escape the sql args. I think the speed difference can be measured in milliseconds - are you really worried about that? if your app is that heavy that you are trying to shave off milliseconds to make it usuable then there are possibly bigger problems. Imagine you have a highly dynamic page that does 50 queries (using the 3rd technique you proposed), I would guesstimate that refactoring the code to do 2-3 less queries per request would get just as much speed increase (if not more) than by refactoring the code to use the 1st technique on all 50 queries (granted you could refactor both but heh there are other things to do than code PHP 24/7 ;-) in order of importance to me (I often have to maintain/update my code): 1. security 2. readability/maintainability 3. speed/performance basically the more abstract your code is, the slower it will be - because you are asking it to do more for you. to me the extra milliseconds wait on a request are worth it, anything to avoid debug/maintainance hell! And if speed really is a big issue - you may need to look into writing part of you application logic as a PHP extension (i.e. in C which is way faster anyway you cut it.) ... Thanks, Alexander PS: All this code is considered to run under magic_quotes_gpc OFF. as it should be ;-) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] String Parsing/Escaping
Jochem Maas wrote: hi Alexander, interesting question regarding 'safety' v. readability v. speed - I'm sure you'll get different views depending on who you ask. Here is my take: Thank you Jochem! :) I rate speed as the least important issue - you can alway use a faster machine, get some more RAM etc if you really need a quick speed fix. making the code faster is the last exercise I do in any given project, and I almost always choose readability/'safety' over speed I know what you mean and also agree generally, however I am nevertheless usually trying to have the code as optimised as possible. If I just knew better Assembler I would probably code all my webstuff in .asm files ;D. Seriously, I prefer to have code as compact, small, efficient and optimised as possible a personal thing I guess. This is a compromise - can't imagine why anyone would choose this one. Well, perhaps because it is a compromise ;). Its readability is much better than with string concatenation however its performance drop is still not that bad because its a native function. YEAH! indeed its the slowest. but its so much more readable and you know its alot more maintainable (you don't have to change the escaping, sprintf'ing strategy in 100 places.). Its safer too because there is no chance of forgetting to escape the sql args. That were also exactly my reasons why I fancied it. Imagine you have a highly dynamic page that does 50 queries (using the 3rd technique you proposed), I would guesstimate that refactoring the code to do 2-3 less queries per request would get just as much speed increase (if not more) than by refactoring the code to use the 1st technique on all 50 queries Thats probably correct. (granted you could refactor both but heh there are other things to do than code PHP 24/7 ;-) I know, I know ;) And if speed really is a big issue - you may need to look into writing part of you application logic as a PHP extension (i.e. in C which is way faster anyway you cut it.) Well, my worries now dont go that far :) Again, thanks very much for sharing your thoughts with me. Alexander -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] String Parsing/Escaping
Hi, below are three versions of an SQL call along with escaping the passed value. $value=mysql_escape_string($_POST['value']); mysql_query('SELECT * FROM table WHERE field='.$value.''); + Fastest Code - Con: Bad Readability, Value needs to be escaped separately $value=mysql_escape_string($_POST['value']); mysql_query(sprintf('SELECT * FROM table WHERE field=%s', $value)); + Good Readability - Value needs to be escaped separately sql_sprintf() is a custom version of sprintf() which automatically escapes all passed parameters. mysql_query(sql_sprintf('SELECT * FROM table WHERE field=%s', $_POST['value'])); + Good Readability, Value does not need to be escaped separately - Slowest Code Up until now I have only used the first version for all SQL work I did. Now however I am seeking for a better and more abstracted solution. I did some quick tests (only for the string parsing, without actual SQL queries) and noticed that the performance (as expected) continually degrades by moving from the top code down the list. While the third version is probably the most secure one due to the fact that sql_sprintf() always checks for escape sequences, it is also the slowest. Especially when the same value is used multiple times because then it is (unnecessarily) escaped again and again for each call, whereas the second version only escapes it once. THIS however is at the same time the big advantage of the third code, because the developer does not need to escape the data manually. Now my question is, what would be a good/the best compromise respectively are there any other solutions for this particular issue? Thanks, Alexander PS: All this code is considered to run under magic_quotes_gpc OFF. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php