   Reverse SQL Validation (SQL Injection)
  (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:$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    }}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:$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;   }    }}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).   
  Security is a process.It can never be solved with technology.Quote:$sql = "SELECT * FROM accounts WHERE username='$username' AND password='$password'";For example, why would attacker bother injecting SQL, when they could just as well try to steal the database itself - and it contains plain-text passwords!!! Remember RockYou? They lost 32 million of them.There is plenty of documentation on how to prevent SQL injection in PHP. Reading through that carefully and perhaps writing a few extra helpers should take care of it.But in above case, that is not the problem. In above, you could try storing a hash with salt, then only comparing against that.But that is just a tiny part of the problem. How to detect malicious intent? Should all requests be logged? All responses? For how long? How would they be analyzed? Who would review them? How are these people verified? How are they audited? How is access to source code controlled? What about admins? Is server running in vault? Are all administrators background checked and monitored? Is there video surveillance on server or data center...At the end of the day, code is the very least of problems, and one that is by far most trivial to solve, yet doesn't mean much with regard to security.   ;  LOL.The usual approach of preventing SQL injection is to sanitize your input and validate it (check if it's in the format and range you expect).Also, you'll normally escape (addslashes() and the like) the strings you're inserting into queries (to make sure the SQL syntax stays as you intend).Your code is a quite cumbersome attempt at validation. I really don't know what you are trying to achieve with that, compared to the usual techniques.   ;  You say performance isn't important but it is even from a security stand point.  Doing what you suggest would make a denial of service attack very easy since it doesn't take much work to make the server do a large amount of processing.Also how do you plan to get those user names and passwords into the database to begin with? No user input means a completely static site.   ;Quote:Original post by -KAS-(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.So, why are you using a database at all? I mean, since you're eliminating the ONE reason to use a database. [grin]Quote:I'll explain better with code:Typical idea:*** Source Snippet Removed ***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.Erm, what? No, that's not the standard way at all.1. You didn't bother to sanitize your input.2. Your passwords aren't salted.3. Account lockout?4. Accountability?Quote:My idea:*** Source Snippet Removed ***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.Right, so, if there are 5000 users, then each time someone logs in you have to compare against that list of 5000 user. The average number of comparisons will be around 2500, per login. I'll attack your servers trivially: I'll simply send false login information, that'll cause you to check ALL 5000 users to discard my bad information. I'll repeat this attack 10 times a second, forcing you to perform 50,000 checks a second. I'll have 100 computers doing this, which will force you to do 5,000,000 checks each second.The proper way to deal with user data, for ANYTHING (not just SQL), is to properly sanitize it. Escape all special characters, force input to be validated, etc. There are PLENTY of functions in PHP (and every other platform) to do this for you, and you can trivially wrap it all up in a set of objects that you abstract your database through.Finally, salting and hashing your passwords doesn't make them secure, it's a preventative measure that makes having a stolen table harder to crack. It doesn't stop replay attacks or anything of the like. Requiring SSL for login and other such information can HELP to reduce the chances of attack, but ultimately any machine you do not have direct control over is a potential attacker, treat them as such.   ;  Thanks for the response.Basically, the idea is, instead of filtering out each character and string, and key word, and reserved word, and charsets and everything one could possibly think of from a string - all the possible invalid combinations already exist: in the database itself in the form of rows that do not exist.The idea of user logins was just an example, please try and look past that. (I pity the coder who even thinks of using that code :D)I will restate with my very last example, as it is more static and uses no data originating from a user.A common approach in many websites is to use a Page Id of some sort, that looks up in a database to get the page data.ie http://www.gamedev.net/community/forums/topic.asp?topic_id=570539Looking past the obvious, check if topic_id is an integer - this is just an example :) - one could get a list of all topic_ids, and compare it to what the user requested. This will ofcourse be slower than simply plugging the users input into the database, but still, even with thousands of rows returned, this is well within the bounds of PHP and MySQL.Simply, no input from the user is safer than any input from a user.-------I would think that 1 line of code (a while loop), would be less cumbersome than the many lines and functions it takes for sanitising. But in saying that, I am not proposing removing or replacing typical sanitising techniques with this idea, merely adding this to our current toolset as a last resort effort.I would not think that this technique would cause a DoS any more than this page would, http://www.gamedev.net/community/forums/activeusers.aspor even hammering this one,http://www.gamedev.net/community/forums/activestats.aspLooping through large rows and printing to a page with styling and formatting is what PHP and MySQL do, so a simple 'if($this_var == $that_var)' shouldn't be too crazy or far out :D   ;Quote:Original post by -KAS-Thanks for the response.Basically, the idea is, instead of filtering out each character and string, and key word, and reserved word, and charsets and everything one could possibly think of from a string - all the possible invalid combinations already exist: in the database itself in the form of rows that do not exist.The idea of user logins was just an example, please try and look past that. (I pity the coder who even thinks of using that code :D)I will restate with my very last example, as it is more static and uses no data originating from a user.A common approach in many websites is to use a Page Id of some sort, that looks up in a database to get the page data.ie http://www.gamedev.net/community/forums/topic.asp?topic_id=570539Looking past the obvious, check if topic_id is an integer - this is just an example :) - one could get a list of all topic_ids, and compare it to what the user requested. This will ofcourse be slower than simply plugging the users input into the database, but still, even with thousands of rows returned, this is well within the bounds of PHP and MySQL.Simply, no input from the user is safer than any input from a user.There are over 500,000 topics in the database. Think about that number for a moment. Now go out and write a php loop that runs through 500,000 iterations doing a comparison against a row fetched from a database or whatnot (don't forget that you then have to fetch the topic data...). Now, launch 30 pages and wait for it to kick back to you with some data. I'll be here when you get back.Quote:-------I would think that 1 line of code (a while loop), would be less cumbersome than the many lines and functions it takes for sanitising. But in saying that, I am not proposing removing or replacing typical sanitising techniques with this idea, merely adding this to our current toolset as a last resort effort.I would not think that this technique would cause a DoS any more than this page would, http://www.gamedev.net/community/forums/activeusers.aspor even hammering this one,http://www.gamedev.net/community/forums/activestats.aspLooping through large rows and printing to a page with styling and formatting is what PHP and MySQL do, so a simple 'if($this_var == $that_var)' shouldn't be too crazy or far out :DYou obviously have NO idea how a database even works, if you think that somehow hammering that page would cause any significant load on the server. Furthermore, your "one line" excuse doesn't even hold water. Sanitizing the input is a one line... ONE FUNCTION call.Databases are OPTIMIZED for fetching data based on keys.Writing stupid code (what you have above) will not make your software more secure. Writing SMARTER code, which can handle the obvious and non-obvious attacks will (such as by sanitizing input, handling invalid login attempts, etc.).   ;  (The example was not to demonstrate user log on, but merely to show a simple SQL statement from any textbook to retrieve data from a database, maybe:$sql = "SELECT my_pony FROM ponies WHERE my_pony = '$userPony' ", would of been better :)I do have the all encompassing, super, magical 'my_filter_function()' sanitising function there, I just thought fellow coders could fill in the blanks as I wanted to save reading time :D (please consider the code pseudo-code that happened to look like PHP :) ));Quote:Original post by -KAS-(The example was not to demonstrate user log on, but merely to show a simple SQL statement from any textbook to retrieve data from a database, maybe:$sql = "SELECT my_pony FROM ponies WHERE my_pony = '$userPony' ", would of been better :)I do have the all encompassing, super, magical 'my_filter_function()' sanitising function there, I just thought fellow coders could fill in the blanks as I wanted to save reading time :D (please consider the code pseudo-code that happened to look like PHP :) ))Maybe I need to make this clearer:If someone on any of my projects wrote a line of code like that.. they would be looking for a new job the next day. That is NOT production code.$query = sprintf("SELECT * FROM ponies WHERE user='%s'", get_magic_quotes_gpc() ? mysql_real_escape_string(stripslashes($user)) : mysql_real_escape_string($user));100% safe (assuming the deprecated magic_quotes_sybase isn't on, but then again, magic_quotes_gpc is deprecated too).Or to make it easier to read, since magic quotes are deprecated, and hopefully you're using a more modern technology:$query = sprintf("SELECT * FROM ponies WHERE user='%s'", mysql_real_escape_string($user));Or the ASP.Net version:var cmd = new SqlCommand("SELECT * FROM ponies WHERE user=@user", connection);cmd.Parameters.Add("@user", userStr);and since you should be using PDOs probably...$stmt = $dbh->prepare("SELECT * FROM ponies WHERE user = :user");$stmt->bindParam(':user', $user);;Quote:Original post by -KAS-I will restate with my very last example, as it is more static and uses no data originating from a user.A common approach in many websites is to use a Page Id of some sort, that looks up in a database to get the page data.ie http://www.gamedev.net/community/forums/topic.asp?topic_id=570539Looking past the obvious, check if topic_id is an integer - this is just an example :) - one could get a list of all topic_ids, and compare it to what the user requested. This will ofcourse be slower than simply plugging the users input into the database, but still, even with thousands of rows returned, this is well within the bounds of PHP and MySQL.Simply, no input from the user is safer than any input from a user.SQL cannot be broken by requesting a non-existent ID. No buffer overflows or such.If IDs are sequential, then any user can just plug in valid numbers 1..n to get any topic - no "hacks" needed. This is one vector of attack, on Facebook ID value of 4 has a special meaning.This starts to fall under authentication, identity management and ACLs or similar, and that is a huge topic in itself.