views:

219

answers:

2

Here is my query:

select * 
  from (select *, 3956 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(45.5200077 - lat)/ 2), 2) + COS(RADIANS(45.5200077)) * COS(RADIANS(lat)) * POWER(SIN(RADIANS(-122.6942014 - lng)/2),2))) AS distance 
          from stops 
      order by distance, route asc) as p 
group by route, dir 
order by distance asc 
   limit 10

This works fine at the command line and in PHPMyAdmin. I'm using Dbslayer to connect to MySQL via my JavaScript backend, and the request is returning a 1064 error.

Here is the encoded DBSlayer request string:

http://localhost:9090/db?{%22SQL%22:%22select%20*%20from%20%28select%20*,%203956%20*%202%20*%20ASIN%28SQRT%28POWER%28SIN%28RADIANS%2845.5200077%20-%20lat%29/%202%29,%202%29%20+%20COS%28RADIANS%2845.5200077%29%29%20*%20COS%28RADIANS%28lat%29%29%20*%20POWER%28SIN%28RADIANS%28-122.6942014%20-%20lng%29/2%29,2%29%29%29%20AS%20distance%20from%20%60stops%60%20order%20by%20%60distance%60,%20%60route%60%20asc%29%20as%20p%20group%20by%20%60route%60,%20%60dir%60%20order%20by%20%60distance%60%20asc%20limit%2010%22}

And the response:

{"MYSQL_ERRNO" : 1064 , "MYSQL_ERROR" : "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(RADIANS(45.5200077)) * COS(RADIANS(lat)) * POWER(SIN(RADIANS(-122.6942014 - lng' at line 1" , "SERVER" : "trimet"}

Thanks!

A: 
...distance%60%20asc%20...

How are you escaping the sql? Looks like you're missing the "route", so the above reads "distance, asc". Looks like it goes downhill from there.

Todd R
object = {SQL: "the query"};request = connection.request('GET', '/db?' + encodeURI(JSON.stringify(object)), {'host': this.host})
hundleyj
The part I pulled out may not be the problem (though it's really hard to read through all the escaped codes), but if the unescaped version works after you manually decode it, then the decoder isn't doing the conversion correctly. Or at least I'd start on that assumption. If that's not it, then it's likely a bug in dbslayer. If it's not that, then it's just MySQL hating you for issuing that query ;)
Todd R
The "+" could totally be it! That should be a "%2B".
Todd R
A: 

A possible immediate source of your problem is the URL encoding. I see the plus operator is transmitted as-is. That's dangerous, because + used to mean space in the traditional encoding. http://www.faqs.org/rfcs/rfc1738

just somebody
This was it! I swapped the encodeURI for encodeURIComponent (didn't know about that one) and this did the trick! Thanks!
hundleyj