2

I'm starting to build an API using Laravel with PostgreSQL as the data source. From what I've read so far, Laravel's Eloquent ORM currently does not support querying indexed JSON (April 2014). For example, the following simple query is not (currently) possible in Eloquent:

SELECT * FROM mytable WHERE json_data->>'firstname' = 'Paul'

As a result, it looks like I'm manually building queries until Eloquent supports it.

As this is an API, we'll need to take data from the URL/cURL query strings: 

https://myapi.com/api/v1/people?firstname=Paul

results in 

SELECT * FROM people WHERE json_data->>'firstname' = 'Paul'

Assuming that I do my best to sanitise the incoming query strings, am I taking a bit of a risk performing straight queries on JSON field data? Seeing as the JSON data type has not been around so long I'm still evaluating it for use within this project.

ArkadePaulG
  • 161
  • 2
  • 10
  • 1
    Certainly not. Just tell me where your API is and if your database has anything worth stealing. – Kyle Hale Apr 03 '14 at 22:34
  • Why say "do my best", as though it will inevitably fall short of the SQL generated by Eloquent? Avoiding SQL injection is not rocket science - use a connection-aware quoting function like `pg_escape_string` or `PDO::quote`, or use a parameterised query (prepare with `WHERE json_date->>'firstname' = ?`). I don't know how you're actually running the query, but if Eloquent actively prevents you from using one or both methods, I'd be very surprised. – IMSoP Apr 03 '14 at 22:37
  • Does Laravel not allow you to use bind variables on arbitrary queries? – jjanes Apr 03 '14 at 22:55
  • 4
    I've never used Laravel, but thanks to [this other question](http://stackoverflow.com/q/22850529/157957) I now know that yes, you can (and should!) run `DB::select('SELECT * FROM people WHERE json_data->>'firstname' = ?', array($user_name))`, and it will use PDO to prepare the query for you. – IMSoP Apr 03 '14 at 23:58

0 Answers0