Server-escaped quotes causes "Column not found: 1054 Unknown column" error

[jmrichardson]

I am running a simple test query:

lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT id WHERE purpose = 'car'"

The error message I am getting is:

google.visualization.Query.setResponse({"version":0.6,"status":"Error","errors":[{"message":"An internal error has occurred","description":"Failed to execute SQL query. SQL error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''car'' in 'where clause'"}]})

The WHERE portion of the SQL is causing the problem. I have a purpose column with 'car' as a possible string value.

However, if I perform the same query but using a number column, it works:

lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT id WHERE term = 36 LIMIT 1"
// Data table response
google.visualization.Query.setResponse({"version":0.6,"status":"OK","sig":"c8997cb7eaa2a24c8344695c19dd1f92","table":{"cols":[{"id":"id","label":"id","type":"number","pattern":""}],"rows":[{"c":[{"v":54734}]}]}})

It looks like a WHERE clause with strings are not working. I have tried "contains" operator but result is invalid:

lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT id WHERE purpose contains 'car'"
// Data table response
google.visualization.Query.setResponse({"version":0.6,"status":"OK","sig":"13d4bfa0321f86f042b34ec79064b316","table":{"cols":[{"id":"id","label":"id","type":"number","pattern":""}],"rows":[]}})

Anything I can do to fix this? I am desperate :) Thanks.

John

[bggardner]

Very strange. The error is not occurring on one of my sites that use the library. I would first guess that back quotes (`) are used around "car" instead of single quotes, but the single quotes are returned in the error. What happens if you switch the order ('car' = purpose)? You may want to back-quote your column names for clarity (and best practices). Also, what version of PHP are you running?

[jmrichardson]

Thanks for your help. So, In my rush to get things working last night, I tried to doctor some of your code because of an error message I was receiving. Today, I downloaded a fresh install and here is the error I get:

$ lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT purpose where purpose = car limit 2"
Fatal error: Call to a member function get() on a non-object in
C:\xampp\htdocs\lcwp\includes\classes\Google\Visualization\DataSource\B
ase\LocaleUtil.php on line 32

I didn't understand the message so I commented line 32 out last night and everything seemed to work (thinking it was some sort of locale transformation thingy). Anyways, now I think it might be causing the issue I originally posted about.

I am running PHP:

PHP Version 5.5.9
System Windows NT JOHNRICHARDSON 6.1 build 7601 (Windows 7 Business Edition Service Pack 1) i586

Really appreciate your help here.
John

[bggardner]

What do you mean by "everything seemed to work"? You shouldn't comment out line 32, as that returns the appropriate error in your locale (currently, only "en" is supported). Instead, please debug it by inserting these lines before line 32 and post the results:

var_dump($locale, $bundleName, $key);
var_dump(ResourceBundle::create($locale, $bundleName, TRUE));
exit();

[jmrichardson]

Sorry about comment that out. You are right, i should not have done that. I meant that after I commented it out, your software seemed to work until i got the problem with the first post above. Here is the output with code before line 32:

$ lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT purpose where purpose = 'car' limit 2"
NULL string(51) "/Google/Visualization/DataSource/Base/ErrorMessages"
string(7) "SIGN_IN" NULL

Thanks for your help

[bggardner]

Hmm...not sure why that's throwing an error. Return Base\LocaleUtil.php to its original code and comment out line 26 of Base\ResponseStatus.php. Let me know if that gets you back to the original "unknown column" error.

[bggardner]

Also var_dump(ResourceBundle::create($locale, $bundleName, TRUE)); should return a ResourceBundle object or FALSE, not NULL. I'll have to dig into that some more.

[jmrichardson]

Yes, commenting out takes me back to the original unknown column error:

$ lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT purpose where purpose = 'car' limit 2"
google.visualization.Query.setResponse({"version":0.6,"status":"Error","errors":[{"message":"An internal error has occurred","description":"Failed to execute SQL query. SQL error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''car'' in 'where clause'"}]})

Even with the columns back ticked, the same problem:

lynx --dump "http://localhost/lcwp/googleVis.php?tq=SELECT purpose where purpose = 'car' limit 2"
google.visualization.Query.setResponse({"version":0.6,"status":"Error","errors":[{"message":"An internal error has occurred","description":"Failed to execute SQL query. SQL error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''car'' in 'where clause'"}]})

Let me know what you think

[jmrichardson]

As a temporary fix for the software to work with my queries, I added a line (140) to MysqlPdoDataSourceHelper.php:

$second = stripslashes(preg_replace("/`/","",$second));

Even though it thinks is a 2 column where clause, I just stripped the back ticks and backslashes to make it work as a column / value filter. Plese let me know if you are able to fix the issue. Great software by the way!! thanks!

[bggardner]

I see, so the parser (Query\Parser\QueryParser.php) parses the WHERE clause as a ColumnColumnFilter. Since I can't replicate the problem on my end, I'm guessing it's either lynx, or more likely, Windows and/or your webserver (I'm running Apache on Linux). One thing you can try is see what query string the library is being sent. Add the following above line 79 in Query\Parser\QueryParser.php and post the result:

var_dump($queryString);
exit();

Next (after undoing the change above), add the following above line 174 in the same file and post the result:

var_dump($filterString, $matches);
exit;

I'm guessing the issue arises from the preg_match() calls in parseNonCompoundFilter(). Thank you for the praise. I'm glad someone other than myself can make use of it!

[jmrichardson]

Here's the first modification result:

string(46) "SELECT purpose where purpose = 'car' limit 2"

Here's the second modification result:

string(17) "purpose = 'car'" array(6) { [0]=> string(2) " =" [1]=>
string(0) "" [2]=> string(0) "" [3]=> string(0) "" [4]=> string(0) ""
[5]=> string(1) "=" }

I am actually going to be moving it to linux/apache in production. My laptop is windows and easier to code php. Will let you know if I run into any issue when in prod. Thanks again...

BTW, I came across this project after looking for a couple of days for something like this. I think you would get more user base if you advertised a bit and created a dedicated web page. Just a thought and keep up the good work!

[bggardner]

Ahh...from the first result, something must be backslash-escaping the single quotes. Since the argument after the equals sign does not start with a quote (single or double), but a backslash, the parser thinks you mean a column name and not a string literal. It may be Lynx, PHP, Windows, or your webserver, but I don't believe it's a true bug with the library. So, a better workaround than your fix to MysqlPdoDataSourceHelper.php would be to do the stripslashes on line 81 of DataSourceRequest.php:

$queryString = stripslashes($_REQUEST[self::QUERY_REQUEST_PARAMETER]);

[jmrichardson]

Sounds good. I updated the code but your suggestion gave a warning:

Warning: preg_replace(): Delimiter must not be alphanumeric or
backslash in
C:\xampp\htdocs\lcwp\includes\classes\Google\Visualization\DataSource\D
ataSourceRequest.php on line 81

I updated it to the following and works:

$queryString = preg_replace("/\'/", "'", $_REQUEST[self::QUERY_REQUEST_PARAMETER]);

Thanks again for your help. It is working beautifully!

[bggardner]

Yeah, I updated my comment right after I posted it to stripslashes() instead, but good catch. Let me know when you switch to Linux/Apache and see if you have the same issue. I'd like to either patch the code or put instructions in the readme.

[jmrichardson]

I moved it to linux/apache without any of the same issues. However, I ran into another one that I will post seperately.

[jmrichardson]

Hi again,

I spoke too soon. I actually didn't fully test the code on apache/linux. I am actually getting the same problem as above:

lynx --dump "http://54.183.2.220/googleVis.php?tq=SELECT id where purpose = 'car'"
google.visualization.Query.setResponse({"version":0.6,"status":"Error","errors":[{"message":"An internal error has occurred","description":"Failed to execute SQL query. SQL error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''car'' in 'where clause'"}]})

Same message in browser as well.

I am wondering if perhaps you made updates to the code and not released it yet here? Just a thought...

Anyways, should I make the same code changes as windows or do you think there is another cause. BTW, i have made sure that the code is not modified at all by me (git clone).

Hope you are having a great Memorial day weekend...

[bggardner]

I think it may be a PHP configuration setting. I'll investigate later this
week. I'm about to throw some ribs on the grill...

On Mon, May 26, 2014 at 4:47 PM, jmrichardson [email protected]:

Hi again,

I spoke too soon. I actually didn't fully test the code on apache/linux. I
am actually getting the same problem as above:

lynx --dump "http://54.183.2.220/googleVis.php?tq=SELECT id where purpose
= 'car'"
google.visualization.Query.setResponse({"version":0.6,"status":"Error","errors":[{"message":"An
internal error has occurred","description":"Failed to execute SQL query.
SQL error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column
''car'' in 'where clause'"}]})

Same message in browser as well.

I am wondering if perhaps you made updates to the code and not released it
yet here? Just a thought...

Anyways, should I make the same code changes as windows or do you think
there is another cause. BTW, i have made sure that the code is not modified
at all by me (git clone).

Hope you are having a great Memorial day weekend...

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/2#issuecomment-44217138
.

[jmrichardson]

Sounds good. I just made the code fix from above and is working. I will test the new release tonight and let you know. I am headed out for dinner with the fam. Thanks!

[bggardner]

Quotes and backslashes are backslash-escaped in REQUEST variables when the PHP magic-quotes-gpc directive is on (on by default), but the directive was removed since PHP 5.4. So, I'm not sure why you're seeing the escaped quotes. I've labeled this issue as a "wontfix" until we can find the cause.