With an input of [email protected] how can I select a record in the database with the below value?
a:4:{s:4:"nome";s:14:"Napoleon";s:5:"email";s:23:"[email protected]";s:19:"conferma_condizioni";s:1:"1";s:2:"ip";s:12:"84.33.92.109";}
I have tried:
$sql_mail = "SELECT * FROM wp_cf7db WHERE data REGEXP '.*'[email protected]';s:[0-9]+:'2'.*'"
The object is compiled with the data type, in your case a string, and the length of the string. You could use something like:
'select *
from table
where data REGEXP concat("s:",' . mb_strlen($email) . ', ":", ?, ";")'
then bind the $email, that should get you pretty close. If an email contains special regex characters though you'll need to escape those. Alternatively a like could be used. Wildcards would only then need to be escaped:
'select *
from table
where data like concat("%s:",' . mb_strlen($email) . ', ":", ?, ";%")'
Your previous attempt also probably could have worked but you needed to fix the quoting:
'.*'[email protected]';s:[0-9]+:'2'.*'
the single quotes are for the regexp encapsulation, additionally the object construction uses double quotes, so you'd want:
'.*"[email protected]";s:[0-9]+:"2".*'
If s:5:"email";s: is always preceding you also could add that to make it a bit stricter.
LIKEwould be sufficient then:SELECT * FROM wp_cf7db WHERE data LIKE '%[email protected]%'esc_like: wordpress.stackexchange.com/a/8847/12496