CODEWITHSUNDEEP
phpmysqlescapingwordpress
marcusnjones
marcusnjones

Reputation: 1085

Escaping a MySQL query containing variables with PHP

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?

Upvotes: 0

Views: 356

Answers (4)

cool
cool

Reputation: 3505

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.

Upvotes: 3

marcusnjones
marcusnjones

Reputation: 1085

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'] )
            )
         );

Upvotes: 0

Marc B
Marc B

Reputation: 360842

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'

Upvotes: 0

beercohol
beercohol

Reputation: 2587

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`" );

Upvotes: 0

Related Questions