Should I still escape my data when inserting to custom table?

Question

I found a similar question, but it didn't really give me the answer I was looking for. This tutorial explains the most important sanitation functions WordPress uses.

I'm using PDO to store form data into custom tables. A lot of this data contains names like l'this and Cotélac & Blancé.

If I put aside e-mail and url fields, is it enough to use esc_attr and esc_html to avid SQL injection and cross site scripting?

Should I use this before data is saved to DB? Or just before I out put it to screen?

//From Wordpress
esc_html -> Escaping for HTML blocks
esc_attr -> Encodes the <, >, &, " and '

This is a typical query I use:

$sql = " SELECT name
         FROM name
         WHERE user_name = :user_name
         AND fk_ID = :some_id ";

$stmt = $this->db->prepare($sql);
$stmt->bindParam(':user_name', $name, PDO::PARAM_STR);
$stmt->bindParam(':some_id', $some_id, PDO::PARAM_STR);
$stmt->execute();

$result = $stmt->fetchAll();
return $result;

// alternative (from another query)
$stmt = $this->db->prepare($sql);
$stmt->execute(array(':id'=> $id));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

return $result;

You might wanna read this: http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection — Wintermute, Mar 27 '13 at 14:14

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Since you've tagged wordpress, I might add that there is the $wpdb class that handles all these.

Yes, and you should use $wpdb->prepare to help you with that.

From the example of the Codex:

$wpdb->query( $wpdb->prepare( 
    "
        INSERT INTO $wpdb->postmeta
        ( post_id, meta_key, meta_value )
        VALUES ( %d, %s, %s )
    ", 
        10, 
    $metakey, 
    $metavalue 
) );

edited May 23 '17 at 11:50

Community

1
1

answered Mar 27 '13 at 14:11

RRikesh

14,112
5
49
70

Yes I know. Unfortunately I thought of this 2 years after I started (and I got to know WP a bit more). So now there are a few thousand lines of SQL queries. On the other hand, not using `$wpdb` allows me to more easily port the code over a different system if the need / time comes. Even if I was using `$wpdb`, I should still escape some of the data? – Steven Mar 27 '13 at 14:15
The `prepare` method will do the necessary for you if you're using `$wpdb`. If in doubt you could hard code some values and try to add it in your wordpress database, just to see that the $wpdb class works how you expect it. – RRikesh Mar 27 '13 at 14:18
Ok. But let's assume I'm not planning on using `$wpdb` just yet, how would you solve my problem above? – Steven Mar 27 '13 at 14:22
Stan gave you a link where this topic is discussed with much details: http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection – RRikesh Mar 27 '13 at 14:28

Should I still escape my data when inserting to custom table?

1 Answers1