I've created a custom table for listing business locations and am using $wpdb->insert() to insert the information into the table. That part is fine, but when I attempt to search the table I'm getting inaccurate results because of the escaping $wpdb->insert() does. The search is choking on any escaped characters.
This is my code for inserting into the table:
$wpdb->insert(
'businesses',
array(
'name' => $_POST[company],
'address1' => $_POST[address1],
'address2' => $_POST[address2],
'city' => $_POST[city],
'state' => $_POST[state],
'zip' => $_POST[zip],
'phone' => $_POST[phone],
'email' => $_POST[email],
'url' => $_POST[url],
'lat' => $_POST[lat],
'lng' => $_POST[lng],
'entered_by' => $_POST[user_id],
'rental_quote' => $_POST[rental_quote]
)
);And this is part of my code for validation prior to input:
$company_name = $_POST['company'];
$company_zip = $_POST['zip'];
$company_id = $wpdb->get_var($wpdb->prepare("
SELECT id FROM businesses
WHERE name LIKE '%$company_name%'
AND zip = '$company_zip' LIMIT 1
"));We're checking to see if a location exists before allowing the $_POST info to be inserted. The problem is when the $company_name variable is something like "O'Sullivans". It's being escaped before being inserted into the table like so: "O\'Sullivans", but when comparing the $_POST data to the table data, it's choking on the escaped apostrophe in the WHERE...LIKE query.
Were I to do a manual search query on the table looking specifically for "O'Sullivans" using a LIKE selector it would be done like so:
SELECT * FROM businesses WHERE name LIKE '%O\\\\\'Sullivans%'
But because we won't know in advance if $company_name will have apostrophes (or any other escaped character), manual double-escaping doesn't work here. I had assumed the $wpdb->prepare()method would handle double-escaping (for the LIKE selector), but it doesn't appear to.
Does anyone know how to handle this?
