qa_db_select_with_pending() is not returning any results when I use an "IN" clause

Question

When I do an extension to Question2Answer, and I use the qa_db_select_with_pending() function, if I have an "IN" clause in my selectspec "source", I get no results back. For example, if I have the following selectspec and I execute it with that function:

                        $tagwords = "'select','jquery','css'";
                        $tagidrows = qa_db_select_with_pending(
                                array(
                                        'columns' => array('^words.wordid'),
                                        'source' => "^words WHERE ^words.word IN ($)",
                                        'arguments' => array($tagwords),
                                )
                        );
 However, if I use an "=" clause in my source, for example,

        'source' => "^words WHERE ^words.word = $",

and I break this into separate queries (one query for each word in this example), I do not have this issue.

When I use my MySQL client, and manually run the query

        SELECT qa_words.wordid FROM qa_words WHERE qa_words.word IN ('select','jquery','css')

then I get results back just fine, no problem. This leads me to believe the query syntax is not the issue.

The same applies if I use the qa_db_single_select function.

Why is this the case?

Answer 1

If you debug down far enough, all Question2Answer PHP database calls route to the function qa_db_query_execute(), which is found in qa-include/qa-db.php in version 1.7. In this function, you will see the line of code where the query is actually executed:

                        $result = $db->query($query);

If on the previous line, you add a debug trace like

                        error_log('[DEBUG] MYSQL query: '.$query);

Then you can monitor your HTTP server's error log (I am using Apache in this case) for all SQL queries that Q2A is running. Caution: You will see a lot of queries! With this enabled, you will see what is happening to your query above when this function executes it:

         [DEBUG] MYSQL query: SELECT qa_words.wordid FROM qa_words WHERE qa_words.word IN ('\\'metonline\\',\\'javascript\\',\\'select\\'')

Because in your selectspec you are passing in a string as your argument, the function is actually escaping it (somewhere between your call to qa_db_select_with_pending and qa_db_query_execute). All single quotes (') in your query become escaped and treated as literal single quotes that MySQL should read. Unfortunately changing this behavior is not straightforward, and requires more than just a little tweak - it is necessary for all other strings with single quotes in them to be escaped, so it doesn't make sense to remove this escaping behavior. 

You're better off breaking the query up for now.

Comments

If you add the DEBUG trace, make sure to turn it off too! It will fill your error logs.