Escaping a MySQL query containing variables with PHP

Question

I'm running the following code on a WordPress website to query the database:

$entries = $wpdb->get_results( "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM `wp_rg_lead_detail` WHERE `value` = \"John\" OR `value` = \"Smith\" OR `value` = \"22101\" ORDER BY `lead_id`" );

I'd like to replace the hard coded values with pre-defined variables. I've attempted to do so using the following code:

$entries = $wpdb->get_results( "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM `wp_rg_lead_detail` WHERE `value` = " . $data['voterdata_FirstName'] . " OR `value` =  " . $data['voterdata_LastName'] . " OR `value` = " . $data['voterdata_VoterZip'] . " ORDER BY `lead_id`" );

Unfortunately, the second code block does not work. I've noticed that the OR and ORDER BY conditions are not being interpreted correctly, but I don't know how to fix the issue. My concatenation looks fine, so I'm assuming the issue is with escape sequences. Can anyone advise a solution?

This is a SQL injection waiting to happen. Take a look at prepared statements and parameter binding, which will also have the side-effect of being easier to read and solving the quoting problem in your query. — David
– David, Commented Aug 14, 2015 at 14:57
Yes indeed. Check $wpdb->prepare() method and $wpdb documentation in general to avoid these kind of things. — cool
– cool, Commented Aug 14, 2015 at 15:26

cool · Accepted Answer · 2015-08-14 15:29:05Z

3

I am not sure what is the question honestly. "Escaping the data for query", or "Making this to work properly" (from perspective issues with quotes), or both.

This way or another, you need to know that $wpdb->get_results() will use $wpdb->query() method (check definition of class here)

Having this in mind, if you check wordpress documentation regarding this subject, you NEED to use $wpdb->prepare() for sure ( rule is: check documentation before implementation).

This will provide us something like:

$wpdb->get_results(
    $wpdb->prepare(
       "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM  `wp_rg_lead_detail` WHERE `value` = %s OR `value` = %s OR `value` = %s ORDER BY `lead_id`", 
        $name1, 
        $name2, 
        $name3 
    )
);

with assumption that names are properly defined above.

edited Aug 14, 2015 at 15:29

answered Aug 14, 2015 at 15:05

cool

3,5453 gold badges39 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

beercohol · Accepted Answer · 2015-08-14 14:55:17Z

You still need to quote your parameters, as they are effectively constants by the time mySQL sees them.

Try this:

$entries = $wpdb->get_results( "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM `wp_rg_lead_detail` WHERE `value` = \"" . $data['voterdata_FirstName'] . "\" OR `value` =  \"" . $data['voterdata_LastName'] . "\" OR `value` = \"" . $data['voterdata_VoterZip'] . "\" ORDER BY `lead_id`" );

Better still, you can replace the ORs with a single IN clause:

$entries = $wpdb->get_results( "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM `wp_rg_lead_detail` WHERE `value` = IN(\"" . $data['voterdata_FirstName'] . "\",  \"" . $data['voterdata_LastName'] . "\", \"" . $data['voterdata_VoterZip'] . "\") ORDER BY `lead_id`" );

Marc B · Accepted Answer · 2015-08-14 14:55:54Z

0

Your queries aren't equivalent - you forgot to quote (and escape) your variables:

[...snip...] OR `value` =  " . $data['voterdata_LastName'] . " [...snip...]
                           ^---------------------------------^--- PHP string delimiters, not sql

So you're generating

OR `value` = foo

instead of

OR `value` = 'foo'

answered Aug 14, 2015 at 14:55

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

marcusnjones · Accepted Answer · 2015-08-14 15:12:45Z

0

Thanks for responding, guys. Here's the solution:

            $entries = $wpdb->get_results( $wpdb->prepare(
            "SELECT `id`, `lead_id`, `form_id`, `field_number`, `value` FROM `wp_rg_lead_detail` WHERE ( `value` = %s OR `value` = %s  OR `value` = %s ) ORDER BY `lead_id`",
            array( $data['voterdata_FirstName'], $data['voterdata_LastName'], $data['voterdata_ZipCode'] )
            )
         );

answered Aug 14, 2015 at 15:12

marcusnjones

1,0751 gold badge10 silver badges18 bronze badges

Collectives™ on Stack Overflow

Escaping a MySQL query containing variables with PHP

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related