Original Post
(I am using PHP and MySQL, but this idea is not limited to either of these.) I am creating an application where Security is more important that Speed, and have been toying around with an idea. Basically, my idea is: preventing SQL Injection attacks (and possibly others), by not inserting any user input into the SQL string to compare what's in the database, rather, compare what's in the database to the users input. I'll explain better with code: Typical idea: That's just the typical way of getting data out of a DB, it could be anything, from getting an employees hourly wage, to getting a list of comments, etc. The user inputs SQL into the database, to get data out. My idea: Basically, we get a list of all users, and compare them against the users input, in PHP. No input from the user ever goes into the database, so Injection can not be possible. Ofcourse, the obvious down side would be performance, if the table is large, constantly returning large amounts of data from the database to PHP and looping through and comparing it all, will be quite slow. So this will be limited to critical parts. Although, with current hardware, this can still be done with a table with thousands of rows, (looping through large amounts of data and printing it all into a table, or whatever, is quite slower than simply comparing strings, and is done all the time). Also, checking for length of user input and things common like that will help out. And also, this can be sped up by trying to predict what the user is requesting, like, sorting the rows by the first letter of the username for example. This technique is limited only to user actions that are simple, like reading from a database, rather than user data insertion. Data insertion, like a user adding a comment, etc, will use typical sanitising techniques. I'm looking for ideas from other views, are there any security risks that I am missing? Surely, giving all the data from a table to PHP would not be too secure, especially if the database is remote from the PHP server. What about buffer overflows, caching, shared hosting, and other things like that? I probably would not use this idea for accounts and logins like in my example, but more like, getting the stats on a sword in a game, or getting a page for a website (/index.php?pageid=445432, get a list of all page ids, and compare it to the GET).
$username = my_filter_function($_POST['username']);
$password = md5($_POST['password']);
$sql = "SELECT * FROM accounts WHERE username='$username' AND password='$password'";
if($result = mysql_query($sql)){
if($row = mysql_fetch_row($result)){
//pass, etc
}
}
$username = $_POST['username'];
$password = md5($_POST['password']);
$sql = "SELECT * FROM accounts";
if($result = mysql_query($sql)){
while($row = mysql_fetch_row($result)){
if(($row['username'] == $username) && ($row['password'] == $password)){
//pass, etc
break;
}
}
}
