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 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
  • 1
    or 1=1 is not a significant signature. you could just as easily evaluate any other value for equality or do anything else that returns true – RobotHumans Feb 10 '11 at 15:44
  • 9
    I can't help but notice that you aren't doing password hashing. Why? – Steve Feb 10 '11 at 17:50
  • 6
    You need to fix more than just the SQL injection. Fix the fact you have plaintext passwords asap. – Incognito Feb 17 '11 at 21:40

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.

| improve this answer | |
  • 1
    XSS is properly handled by encoding the text that comes from or that is interpolated with text that comes from the user. How to encode text depends on the context (encoding output for JavaScript is different from encoding output for an XML attribute value). "Cleaning the data" is a hack, which often turns out to be easier than encoding text for output when your language/stdlib/framework doesn't encode text automatically and doesn't provide a convenient way to encode text manually. – yfeldblum Feb 10 '11 at 14:34

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...

| improve this answer | |

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.

| improve this answer | |
  • 1
    yes, but I can test against those values when evaluating a injection protection mechanism – Steve Feb 11 '11 at 10:19
  • 1
    If what you want is information about how to test whether your site contains any SQL injection vulnerabilities, that's a different question -- and if that's what you wanted to ask, I suggest you ask that question (and ask it in a separate question). The answer is not necessarily going to be of the form "build up a laundry list of potential attack vectors and try each of them one by one". In other words, a well-posed question should specify what you want to achieve, without making assumptions about how best to achieve it. – D.W. Feb 12 '11 at 2:22

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.