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.
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.
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?
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.
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?
If you know of an approach that overcomes these problems, I would really really like to know - this is a difficult problem.
mysql_real_escape_string() in sightValues 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.
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.
mysqliHow 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.
Right. Changed to code to use eval() (shudder) which solved the problem. Needs security review.
Will this approach protect against SQL Injection Attacks while overcoming the problems of no parameterized SELECT and mysqli's parameter limitations?
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:
=, <>, >, LIKE, NULL, IS.AND, OR, XOR, NOT' quotes and you will need to feed these through mysql_real_escape_string() to make sure there are no shenanigans.