2

Does RedQueryBuilder api, which allows access to the tree of the query, not to the finished sql? I want to write secure way to receive SQL requests from the client, forming the final SQL on server. For example, the client may send:

{
    op: "AND",
    left: {
        op: "=",
        left: "name",
        right: "Bob"
    },
    right: {
        op: "<",
        left: "time",
        right: "1300000000" // now() timestamp
    }
}
Somewater
  • 113
  • 1
  • 7

2 Answers2

1

Finally, I use the SQL parser on the client side to construct conditions tree and send to server like json.

Its my simplified administration interface: https://gist.github.com/Somewater/5705567

in a nutshell: 
1) ReQueryBulder generate SQL request (string)
2) SQL Parser create SQL request structure
3) My code convert SQL Parser structure to simple json (as stated above)
4) send json to the server

And server request parser (ruby): https://gist.github.com/Somewater/5705620

And I can handle request on server like this (Rails request controller in my example):

include ConditionsBuilder
cond = JSON.parse(request.params['cond']) # conditions like json string
condTree = self.parse_sql_conditions_from_json(cond) # conditions like btree structure

# perform some conditions checks, for example, add additional conditions
permission_conds = And[ GtEq['permissions', 12], Eq['is_admin', 1] ]
condTree = And[condTree, permission_conds]


conditions = self.build_sql_conditions((Time.new - 90.days).to_i, Time.new.to_i, condTree)
# handle sql request: "SELECT * FROM tablename WHERE " + conditions.to_s
Somewater
  • 113
  • 1
  • 7
0

I'm afraid it doesn't.

In your example if "now()" is a a SQL fragment you would still have security issues?

I'd suggest that you see what options your database gives you to totally not trust the SQL. E.g. very low power user and only select against views

or you re-parse the SQL to check its contents (this seems like a poor man's version of using your databases to constrain the SQL).

Maybe add SQL as a tag? Might be a bit of religious war about allowing untrusted SQL to hit your database.

A further worry is to protect against DOS so the database may also be best placed to do resource limitation. e.g. client sends (in any format) a hideous Cartesian join.

salk31
  • 995
  • 2
  • 8
  • 13
  • No, "now()" fragment is only bad example, I fix it. – Somewater Jun 03 '13 at 10:44
  • What do you mean by "SQL as a tag"? – Somewater Jun 03 '13 at 10:47
  • I plan to use only WHERE request part. So I don't worry about danger joins etc. My subject area includes requests only to a single table – Somewater Jun 03 '13 at 10:54
  • Re: "SQL" I think the SQL/RDBMS gurus on stackoverflow might be best placed to comment on the wisdom/practicality of using arbitrary SQL. – salk31 Jun 03 '13 at 11:34
  • Re: "WHERE part" would it be possible to use a SQL Parser on the server side? That way you can use a standard format (rather than a new invention). You could/should then get an object tree to fiddle with and construct safe SQL? – salk31 Jun 03 '13 at 11:35
  • Thanks, your suggestion usefull for me. Yes, I can use some SQL parser on server side too. My initial idea is: 1) user use RedQueryBuilder to form your request conditions 2) javascript get conditions tree like JSON and send to server 3) server parse conditions tree (check some specific rights, add some specific conditions etc...) and generate, execute SQL request, if possible – Somewater Jun 03 '13 at 12:16
  • Sticking to using SQL and doing the complicated bit on the server seems good to me. Maybe you could share how you get on? I've worked at a few places that have allowed some form of arbitrary SQL and locking down different RDBMS is not fun. Being able to on the fly fiddle/analyze the SQL could be useful too. – salk31 Jun 03 '13 at 14:15