How to make MySQL search queries with quotes

Question

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.

Answer 1

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.