[wp-hackers] escape data before db insert?

[Mark Jaquith]

On Jan 17, 2007, at 1:13 PM, Bjorn Wijers wrote:

> Now my question is: Do I need to manually call $wpdb->escape() on  
> every piece of data I would like to insert into the database or not.

No.  GPC will be pre-slashed for you, just as if magic_quotes_gpc  
were on.  Values from the database will have to be reslashed before  
going back in.  If you're modifying GPC input, you should stripslashes 
(), modify, then $wpdb->escape().

I recently clarified this in the codex (it had incorrect information):

http://codex.wordpress.org/Function_Reference/wpdb_Class#escape_- 
_Escape_For_SQL_Queries

> $string = "O'Reilly" (comes in via $_GET or $_POST)
>
> after I use
>
> $wpdb->escape()
>
> becomes after retrieving it from the database and echo'ing it:
>
> O\'Reilly
>
> So I presume it is already escaped before I used $wpdb->escape()  
> and the extra $wpdb->escape() changes it into O\\'Reilly before  
> inserting it into the database.

It started as O\'Reilly and then the additional manual escaping made  
it O\\\'Reilly (yes, three slashes... slashes are escaped by slashes,  
so the first two slashes come out as one slash and the third slash  
and the quote come out as a quote, giving you back the original O 
\'Reilly)

So, as an exercise:

<?php

// populate from $_GET
$GET_test = $_GET['test'];

// populate from DB
$DB_test = $wpdb->get_var("SELECT something FROM sometable WHERE foo  
= 'bar'");

// now prepare both values for a query
$DB_test_safe = $wpdb->escape($DB_test);
$GET_test_safe = $GET_test; // Already safe

$wpdb->query("INSERT INTO something (test1, test2) VALUES 
('$DB_test_safe', '$GET_test_safe');");
?>

Here are my rules:

1. anything that would be escaped by magic_quotes_gpc can be assumed  
to be escaped in WordPress and can be safely used in queries **so  
long as the original superglobal has not been manipulated**

2. manipulations of GPC superglobals should be done on copies,  
leaving the originals escaped

3. manipulations of copies of GPC superglobals should be done after  
running stripslashes() (so you're working with the data, not the  
escaped data)

4. manipulated copies of GPC superglobals MUST be re-escaped using  
$wpdb->escape() before being used in SQL queries of any kind.

5. All data not originating from GPC must be assumed to be unsafe,  
including stuff from the database.

--
Mark Jaquith
http://markjaquith.com/

Covered Web Services
http://covered.be/