Simple sql injection solution for login forms?

Question

I saw several examples and people using this way to query the database in a login form. I'm not fully sure is this is the best way to do a login form secure.

This is the query in PHP:

    $query = "SELECT * FROM users WHERE usern = '".$_POST['username']."' AND passw = '".md5($_POST['password'])."'";

Is enough having md5() on the password post to avoid sql injection?. I think that the md5 function will convert all characters and sql strings to a 32 char string.

Which other ways can I protect the login form?

Michael · Accepted Answer · 2012-03-30T15:50:58.583

6

mysql_real_escape_string($_POST['username']), etc.

Although it's better to use the mysqli extension and use prepared statements.

(Assuming you're using MySQL)

Edit: In response to the comment below, it might be good to use this for LIKE queries:

addcslashes(mysql_real_escape_string($_POST['username']), '%_')

edited Mar 30 '12 at 15:50

answered Mar 30 '12 at 15:39

Michael

11,912
6
49
64

1

+1 for prepared statements - it is much better to pass the data you're about to insert as **data** than to have SQL strings you wrote mixed with untrusted strings coming from the user – Deebster Mar 30 '12 at 15:41
definately +1 for the prepared statements... mind you **"mysql_real_escape_string()"** does not escape "%". check out the notes section in php.net/manual/en/function.mysql-real-escape-string.php – Kishor Kundan Mar 30 '12 at 15:45
1

Yes, although that's only an issue when using `LIKE`. You can use `addcslashes()` to help with that. – Michael Mar 30 '12 at 15:48

score 0 · Answer 2 · answered Sep 27 '14 at 04:49

Just one simple solution use parameters for fields like username and password so that SQL command string is separably sent from the parameters and then attacker will only get blank responses.When parameters are used SQL command string is parsed and compiled separately from the parameters. Using prepared statements is the best solution.

score 0 · Answer 3 · edited May 23 '17 at 11:52

You must sanitize your data before you let it near your database. The simplest way to do this is by using mysql_real_escape_string($_POST['username']) but this is only the very least you need to do.

If you're using a framework like CodeIgniter, you can use their in-build functionality which strips $_POST or $_GET inputs of any XSS risk. Otherwise, I'd recommend these posts:

score 0 · Answer 4 · answered Mar 30 '12 at 15:40

0

You need to escape $_POST['username'] as well

and yes md5 will protect you from sql injection.

For this example, something like this would be ok

$query = "SELECT * FROM users WHERE MD5(usern) = '".md5($_POST['username'])."' AND passw = '".md5($_POST['password'])."'";

answered Mar 30 '12 at 15:40

ahmetunal

3,930
1
23
26

not a bright idea I know, responded within the context of the question, but instead of comparing usernames, compare md5's of usernames.At least, this wayi you dont need to worry about the injection for login. – ahmetunal Mar 30 '12 at 15:50
Until he tries to do an `INSERT` or an `UPDATE` to `SET` the `usern`. :) – Michael Mar 30 '12 at 15:52

score 0 · Answer 5 · answered Mar 30 '12 at 15:41

The way you have build your query easily allows to inject pieces of code in the username. You can use prepared statements to avoid that: http://php.net/manual/en/pdo.prepared-statements.php

Prepared statements basically will describe how the statement will be structured, and adds the data afterwards. This way, the user can not alter the structure of the statement with the input data.

score 0 · Answer 6 · answered Mar 30 '12 at 15:41

If you make a function which sanitizes all POSTS and GETS you are safe

function clean() { 
    foreach($_POST as $key => $val) { 
        $_POST[$key] = mysql_real_escape_string($val);
    } 
}

You can also use PDO and statements with variables, and PDO will clean automatically.

score 0 · Answer 7 · edited May 23 '17 at 11:52

<?php

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$db", $dbusername, $dbpassword);

    $query = "SELECT * FROM users WHERE usern = ? AND passw = ?";
    $sth=$dbh->prepare($sql);
    $sth->execute(array($_POST['username'], md5($_POST['password']));
    $result =  $sth->fetch();
}
} catch(PDOException $e) {
    echo $e->getMessage();
    exit;
}

PDO is the Best way to stop SQL Injection in PHP

Simple sql injection solution for login forms?

7 Answers7