Re: MySql Injection advice

tedd wrote:

At 3:53 PM -0400 7/12/09, Paul M Foster wrote:

On Sun, Jul 12, 2009 at 09:07:45AM -0400, tedd wrote: <snip>

As for prepared statements, I'm no authority on them, but from what I've read they are not going to be something I'll be practicing anytime soon.

Aside from Stuart's comments about slowness, what else have you read that makes you discount the use of prepared statements? The PDO class emphasizes that you're safe from SQL injection exploits, which seems a big plus. Paul

Paul: As I said, I'm no authority. However as I have read, prepared statements are for a limited set of instructions in MySQL. They can't be used for everything. Why should I learn one way to do something that isn't universal in the language?

They are useful for select, insert, and update queries, which are the three most common types of queries in web applications and are most often used for SQL injection. I personally use the MDB2 database abstration layer. Here's how it's done - $types = Array('integer','text'); $q = "SELECT something,else FROM table WHERE id < ? AND type=?" $sql = $mdb2->prepare($q,$types,MDB2_PREPARE_RESULT); $args = Array($someinput,$someotherinput); $rs = $sql->execute($args); Here's the non prepared way $sql = "SELECT something,else FROM table WHERE id < $someinput AND type='$someotherinput'" $rs = $mdb2->query($sql); The two are very similar syntax, just a few extra steps required for prepared statements - and if the query is performed multiple times with different arguments, you can re-use the prepared statement and don't have to make it again. The first has sql injection protection automatically for the two arguments, the second requires that you first sanitize the two arguments - which is where mysql_real_escape_string comes in - but as soon as you use that mysql specific function, your code no longer is as easily portable to other databases. Prepared statements may be a minor performance hit but I suspect if it is even noticable, you are at the edge of what your server can handle and either need hardware update, infrastructure update (IE dedicated sql servers and load balancing), or code optimization that probably will find bigger issues than sql prepared statements. Using a cache (IE APC or memcached) for commonly performed queries makes the speed difference between the two only matter when the query isn't cached.