Re: does "quote" DB filter out all dubious characters preventing sql injection?

* Justin Patrin <papercrane@gmail.com>: > On Thu, 7 Oct 2004 14:43:08 -0700 (PDT), Stowe Spivey > <spiveyspivey@yahoo.com> wrote: > > I'd like to switch to using Pear but need to know how far I need to > > go in securing the use input using DB pkg. > > Well, the correct function now is quoteSmart(). It completely quotes > the value, making sure that it goes into the DB as given. It stops SQL > injection. For example: > > $val = 'blah";DELETE FROM table'; > $db = DB::connect($dsn); > $sth = $db->query('SELECT * FROM table WHERE column='.$db->quoteSmart($val)); > > This will search for "column" equal to exactly the string in $val. > Also, there is a quoteIdentifier function which should be used for > quoting table and column names. You only need to use this if you're > using a reserved word for a table or column name, but it's useful > otherwise too. > > $table = 'select'; > $column = 'from'; > > $sth = $db->query('SELECT * FROM '.$db->quoteIdentifier($table).' > WHERE '.$db->quoteIdentifier($col).'='.$db->quoteSmart($val)); You forgot to point out another option: use placeholders. Read the PEAR::DB documentation regarding prepare and execute -- you can use the placeholders they detail in most of the PEAR::DB methods (I've used them with query() and the get*() methods) -- and this eliminates the need to use the quoteSmart() method in most cases. As an example, in the example above, you could use: $sth = $db->query('SELECT * FROM table wHERE column = ?', array($val)); And in the second one: $sth = $db->query('SELECT * FROM ? WHERE ? = ?', array($table, $col, $val)); If the values already contain their quotes (e.g., if magic_quotes is on), then PEAR::DB won't re-escape them; if not, it will escape them before placing them in the SQL. -- Matthew Weier O'Phinney | mailto:matthew@garden.org Webmaster and IT Specialist | http://www.garden.org National Gardening Association | http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org