SQL injection for a username/password form?

Question

This is how user validation happens on my site:

if(!empty($_POST['username']) && !empty($_POST['password']))
{       
    $query = "SELECT * FROM users WHERE username='".$_POST['username']."' AND binary password='".$_POST['password']."'";

[I do however have this injection cleanup code running - Is this enough to tackle script / malicious code insertion in GET, POST requests? on every page that my site loads.]

However, I'm trying to develop a "list" of 'attacks' of clever username/password combinations that would test whether I can get unauthorized access into the system.

Stuff like:

1. %%, %%
2. *, *
3. ' OR '1'='1, ' OR '1'='1

Answer 1

As long as you use dynamic SQL, SQL-Injection stays an open attack vector. If you want to secure against SQL-Injection, the way to go is by using parametrized queries. I suspect you are using PHP+MySQL, so this would be an example of a parametrized query.

$dbh = new PDO('mysql:host=localhost;dbname=example', 'user', 'password');
$sth = $dbh->prepare('SELECT * FROM users WHERE username=? AND binary password=?');
$sth->execute(array($_POST['username'], $_POST['password']));
$result = $sth->fetch_all();

Of course, stripping the user input from illegal characters might still be something you want to do because even though your site might be protected from SQL-Injection, you users might still get attacked via XSS. So I suggest a combination of both techniques.

Answer 2

I would agree with mike, that by using parameterized queries you can get rid of SQL injections, but again that depends on how they are built.

I would say that using a whitelist is always better than using a blacklist. When we try to list kinds of attacks, there would always be a chance that an attack skips through. Like you mentioned filtering 1=1 but then a=a, me=me will produce same result.... Therefore whitelisting would help where initially you just accept what you want and discard others by using regex...then parameterized queries...

Answer 3

The best answer to this question is to un-ask the question. The code you show is vulnerable, and the only reasonable way to address the vulnerability is to fix the code. The best fix is to use a prepared statement or parametrized query: do not use string concatenation to build up SQL queries. Collecting examples of attacks is not going to lead to an effective defense against SQL injection attacks. Odds are, there will always be one more attack not on your list.

Answer 4

I agree with the above answers - just fix the code to be safe. But I also recommend some kind of data collection from the live site, so that someone that is attempting the attacks can be banned.