Re: MySql Injection advice

Things I have used prepared statements for: 1. SELECT 2. UPDATE 3. INSERT 4. DELETE 5. Stored procedures Things I am aware of that prepared statements are not capable of doing: What have you read that prepared statements can't do? I've not heard of anything, nor have I encountered anything, myself. And given that I am prone to making errors, I like the fact that my work flow prevents a mistake I make leading to an unnoticed vulnerability. On Mon, Jul 13, 2009 at 5:00 PM, Michael A. Peters<mpeters@mac.com> wrote: > 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. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >