-1

I have a Single Page Application in which the browser does all the logic work. Except for initial loading, the server is pretty much a fancy interface to the database.

The browser sends data dictionary keys, column name / value pairs, and where clauses for SELECT, for example. The server assembles the parts into SQL, executes the queries, and replies. NEW: In a SELECT, for example, the table name and columns pulled are from the data dictionary - the browser supplies the data dictionary key and the SELECT where clause.

This very open environment is very susceptible to SQL Injection attacks. The goal is to prevent damage from said attacks.

Problems to be Overcome

First, as discussed, it is impossible to paramaterize a random SELECT where clause - SELECT cannot use a prepared statements.

Second, mysqli, the library for paramaterized statements with MySQL, does not support NULL nor MySQL functions, eg, CURRENT_DATE or NOW(), as discussed.

Proposed Solution

First, if SELECT cannot be paramterized, then execute SELECT by a user who has no DML or DDL rights. This will prevent SQL Injection Attacks from changing the database.

Second, write a wrapper function for mysqli that will allow NULL and MySQL functions to be passed as parameters. This will allow parameters to be easily used for all DML.

Third, shadow highly sensitive data where it cannot be seen or touched by normal queries or normal users. This will put sensitive data, such as passwords, out of the range of attacks.

Forth, write a wrapper order to enforce the user / query type relationship. This will ensure SELECT are executed by the select user, for example

The result of that effort is here. The question is, logically, will this approach successfully protect against SQL Injection Attacks?

Non-Answer Answers

I proposed this same question before. Due to my poor job of presentation, received answers and comments ended up focusing on spurious issues instead of addressing the (admittedly) difficult question - does this actually work?

As a reference, here are some of those comments and answers.

Use PDO's prepared statements

First, prepared statements cannot be used for all SELECTs - how does PDO help? Second, mysqli does not accept NULL or MySQL functions - how does PDO help?

Why re-invent the wheel

If you know of an approach that overcomes these problems, I would really really like to know - this is a difficult problem.

no mysql_real_escape_string() in sight

Values should be sanitized before being passed to the database query functions. mysql_real_escape_string() is one of a set of sanitizing functions available, for example, one would use a different sanitizer for dates.

too much work

Please share with me your know of any approach that overcomes these problems - I would really like better insight. That said, from my setting up the whole thing again, following my notes it took between 30 and 45 minutes. No time costs incurred thereafter.

I am happy with mysqli

How are you going to prevent SQL Injection Attacks when you cannot parameterize your SELECT? Do you expect to never use NULL when updating a column? Every man to his own poison, but these are issues I hope to solve - not live with.

@Konerack pointing out limited number of parameters

Right. Changed to code to use eval() (shudder) which solved the problem. Needs security review.

The Question Again

Will this approach protect against SQL Injection Attacks while overcoming the problems of no parameterized SELECT and mysqli's parameter limitations?

Community
  • 1
  • 1
cc young
  • 18,939
  • 31
  • 90
  • 148
  • `Will this approach protect against SQL Injection Attacks`? No it will not. – Johan Sep 14 '11 at 10:15
  • @Johan - if it will not protect, I would love to hear a concrete example so I can get my head around it! – cc young Sep 14 '11 at 10:17
  • 1
    I don't know why, but it's a terrible idea to have dynamic table / column names being sent by the browser. I don't know what's the problem you're trying to solve, but this looks like the wrong way. – N.B. Sep 14 '11 at 10:17
  • @N.B. - _all_ the work is done in the browser! For example, in interaction with the user, the browser knows we need a list of employees where this and that. It asks the server to retrieve it. – cc young Sep 14 '11 at 10:19
  • So what? What's wrong with browser telling your server "invoke stored procedure to retrieve user data" rather than "hey, select x, y, z from where "? I'm sorry to say this, but you took the wrong approach. In the end, browser has to know what table / column / where clause it needs to send. Therefore, it's no more work for it to know what procedure should be invoked to obtain the same data. Your design approach is wrong and you saw one huge limitation - cleaning data and exposing your structure. – N.B. Sep 14 '11 at 10:23
  • Move to a framework, like CI or CakePHP. I was in the same boat as you before I moved to CI. I had developed CSRF, XSS and using PDO to stop injection. I was always worrying if my code was robust enough. Now I have moved over to CI, I don't even worry about this stuff. – PaulM Sep 14 '11 at 10:34
  • @N.B. - did not quite follow all. Many years ago (using Oracle) used stored procedures to access data. Using data dictionary to achieve similar result, but not same speed or security. In future could use same dd to build the procedures for DML (but not SELECT!). The `where clause` of a select cannot be parameterized. True, the structure is exposed - the code itself is open source, so this has always been the case. Not sharing your abiding sense of pessimism. – cc young Sep 14 '11 at 10:44
  • @cc young - I'm not being pessimistic. You can achieve all you need via stored procedures, without having browser exposing your data. If you explain why you're using this approach, I'll gladly share what I think you might do, with more security, speed and reliability in mind. – N.B. Sep 14 '11 at 10:48
  • @N.B. - would love to, thank you for the invitation. Do you know how to make this a chat, or could use google chat - I rest as bangkok-maco there. – cc young Sep 14 '11 at 10:53
  • @PaulM - thanks for the suggestion. Either would have been nice to use earlier. This app is almost all javascript on the browser. Server side minimal, not at all married to PHP, eg, soon will run on node.js as well, so very interested in small, well defined libraries I can move back and forth. – cc young Sep 14 '11 at 11:05
  • You had an initial goal and you are asking how to secure your chosen solution (implementation / algorithm). Maybe you could get better results and discussion by asking the question posed by your orginal goal and discuss other potential solutions that achieve this goal. It may be the case that the solution you have chosen is not the most elegant or trouble-free of the available options. – Cheekysoft Sep 14 '11 at 13:26
  • @Cheekysoft - good idea (I hope). will post question as such. – cc young Sep 14 '11 at 18:04

1 Answers1

1

The answer is simple:

For parameters use PDO or mysql_real_escape_string().

For other dynamic SQL (table names, column names, syntax elements) use whitelisting.

See: How to prevent SQL injection with dynamic tablenames?

You cannot trust the browser to keep within the bounds you set in Javascript. This can be easily manipulated. So you must treat all data from that end as untrusted. Make sure you check all elements in the where clause against a list of allowed keywords.

For this purpose you'd use:

  1. Check against whitelist for symbols: =, <>, >, LIKE, NULL, IS.
  2. Check against a whitelist for boolean operators: AND, OR, XOR, NOT
  3. All values must be properly enclosed in single ' quotes and you will need to feed these through mysql_real_escape_string() to make sure there are no shenanigans.
  4. All values not in (1,2) and not enclosed in single quotes are column names, check them against a whitelist of allowed column names.
  5. Reject all other input.
Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • I do not think you read the question! If a SELECT `where clause` cannot be parameterized, how can you whitelist it? If `mysqli` cannot accept NULL or MySQL functions, how will PDO help? – cc young Sep 14 '11 at 10:12
  • I read the question. if you do a `select $var FROM table1` you need to check the $var against a whitelist of allowed column names. Please read the link. – Johan Sep 14 '11 at 10:14
  • Did read the link - good work! First, the columns to be selected are not set by the browser but part of the data dictionary, so no white list necessary. Second, if the `where clause` cannot be parameterized then it cannot be white listed, so we still have the problem – cc young Sep 14 '11 at 10:26
  • as [discussed](http://stackoverflow.com/questions/7291630/sql-injection-how-to-sanitize-program-generated-sql-clause) - see examples, eg, " is distinct from " - I still maintain correctly white listing a where clause would require a complete SQL parser. Note that it's ok if the query fails - what's not ok is if the SQL Injection Attack does harm. – cc young Sep 14 '11 at 11:33
  • @cc young, come to think of it there is another way. Stackexchange allows custom queries on its database (a copy of it actually) you'd have to check how this is secured allowing only access to a subset of data and only select should go a long way – Johan Sep 14 '11 at 18:11