Yes, that's a bit sad. There's always a easy way to mess up with vulnerable code.
Creating a system that checks if there are possible SQL injection vulnerabilities could be more secure, but more CPU intensive though.
I mean:
Code: Select all
INSERT INTO a (a,b,c,d) VALUES (sdfds,'sdfsf,45'45,dfg)
Then I would check that there are no invalid characters. For example I could check on DAL level if the metacharacters are escaped. But this becomes a bit slow and crazy.
EDIT: I got an idea. And I would like to hear your and everybody's opinion.
Let's say I want to insert into the db. The correct syntax is somewhat like this:
Code: Select all
INSERT INTO table (rowname,rowname2,...) VALUES (rowvalue,rowvalue2,...);
Whenever your prepare() the statement, the DAL would check whether the user has anything else except ? characters inside the VALUES -parenthesis. Basically this is invalid:
Code: Select all
$db -> prepare("INSERT INTO table (name,email) VALUES ('mordred','sorryforusingurname');");
$db -> exec();
The following code could throw an error, because you have inserted other than ? or , characters inside the parenthesis. I could basically try to make a little and as fast as possible check for common SQL clauses. This would prevent usage of the above code. Instead I would trigger an error "You are not querying the right way.. blah blah".
There's one problem, this is just a common case. Anyone could do something like:
Code: Select all
// Database manager plugin for my project
$table_to_manage = $_GET['table'];
$db -> prepare("SELECT * FROM $table_to_manage;");
$db -> exec();
The previous idea was good for only certain situations, but now I am dead. There's nothing I can do about the above code :'( ... ?
And btw, do you think using ¤ instead of ? as a 'metacharacter' is a good idea? Because ? might cause problems:
Code: Select all
$db -> prepare("SELECT * FROM table WHERE match REGEXP ('[a-z]?')");
$db -> exec();
I dont remember from the top of my head how the REGEXP clause in SQL went but if something like that is passed the ? could cause troubles, so using ¤ could be a good idea?