0

Hi everybody I run a wordpress based website and I'd like to store the search queries called by users to use them as analytics data to know what's most searched etc... So I have a custom table where the rows are like

query => the searched query
counter => how many times this query has been searched for

So everything works fine, the only problem I encountered is with apostrophe.

I need to check in the database if the query already exists, because if it already exists i just update the counter, otherwise I insert a new row.

I use the default get_query_var('s') that I believe is automatically escaped by wordpress.

The simple SQL query that I use to check if the searched query is already stored in the database is this:

$search_query = get_query_var('s');

$wpdb->get_row("SELECT * FROM $table WHERE query = '$search_query'");

if( $wpdb->num_rows == 0 ) { // insert... }
else { // update... }

I have reason to think that the variable is fine since it's stored correctly in the database, without any additional slash. It's just the SQL query I run that doesn't work, it returns no results and therefore it keeps inserting new rows with the same query value.

I'm open to any solution, even using LIKE but I believe there's something that I'm missing and that the solution is easier than I thought, and I'd rather keep using the = operator since I need to match the exact query. Thank you very much in advance all of you.

P.S. It works fine with queries without quotes, it inserts the new queries and updates the existing ones.

0

Woah there. You've just opened up a can of SQL injection.

I use the default get_query_var('s') that I believe is automatically escaped by wordpress.

Not quite - get_search_query() will do that, but get_query_var( 's' ) gets the "raw" value.

Regardless, always use wpdb::prepare or similar escaping before executing SQL:

$query = $wpdb->prepare( "SELECT * FROM $table WHERE query = %s", $search_query );
$item  = $wpdb->get_row( $query );

if ( $item === null ) {
     $wpdb->insert( $table, [ 'query' => $search_query ] );
}

Check out the awesome helper method wpdb::insert too.

  • Thank you very much. In fact I wasn't quite sure about get_query_var('s') being already escaped, thank you for clarifying that. However I just tried the same SQL query with $_GET['s'] (I know this is even more open to injection, but I did it just to try) and it worked... so get_query_var('s') must do something to the variable, right? Otherwise I would get the same result if I run with get_query_var('s') or with $_GET['s']. Anyway thank you again for your answer, couldn't have been better. – Ernie Bob Jun 22 '16 at 14:17
  • For legacy reasons, WordPress forces magic quotes, which means your $_GET/POST/REQUEST vars will have quotes pre-escaped. get_query_var is a wrapper for retrieving parsed vars from the request, which have since had the slashes stripped. – TheDeadMedic Jun 22 '16 at 14:33
  • Thank you very much for this clarification, it was a huge basic information I was missing. Can I ask you one last quick question? I only knew about $_POST being already escaped, therefore to insert custom data in the database retrieved from a user form, I do this: $_POST = stripslashes_deep( $_POST ) and then I use the $wpdb->insert() which automatically prepares. Is this correct? How would you do it? – Ernie Bob Jun 22 '16 at 14:36
  • Yes, that is the minimum amount of sanitizing you need. But I hope you're not just storing the entire contents of $_POST? And that you are sanitizing/validating individual fields? Don't forget that preparing for SQL is only half the war - you still need to protect against XSS and ensure that HTML is either stripped/escaped/limited, either before saving and/or just before displaying (echo'ing). – TheDeadMedic Jun 22 '16 at 14:40
  • No haha of course I don't store the entire content of $_POST, but since I handle multiple fields, i call the stripslashes_deep() on the entire $_POST, then handle the fields one by one... anyway since this is a contact form and I will be the only one reading the content, I don't escape the HTML when inserting, only when printing it. Thank you again very much, have a good day. – Ernie Bob Jun 22 '16 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.