sql injection question

[dazzclub]

I've always been worried about my first php/mysql project being attacked via SQL Injection. I've read about numorous ways of sanitising any input.

 

So i would like to run a query by you guys and girls.

 

i use  mysql_real_escape_string only on my username, i now know this should be used for the password. Is there anything else you could do, for more security reasons. My knowledge on this section is rather small so apologies for an errors in my post.

 

here is how the login script works:

  // validate username
    if(isset($_POST['username']) && !empty($_POST['username']))
    {
        // use the built in mysql real escape string function to protect agains SQL Injection
        $username = mysql_real_escape_string($_POST['username']);
    }
    else
    {
        // username does not validate, define an error
	$no_username = '<div class=\"login\" >incorrrect username</div>';
        
    }

    // we apply the same for the password field.
    if(isset($_POST['password']) && !empty($_POST['password']))
    {
        $password = md5($_POST['password']);
    }
    else
    {
        $no_password = 'Password not provided';
    }

    // chekc that no errors have been set, if so display them
    if(isset($errors) && is_array($errors))
    {
        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';
    }
    // no errors are set so we'll continue
    else
    {
        $sql= " SELECT * FROM members WHERE username = '$username' AND password = '$password' ";
        $result = mysql_query($sql) or die('Query Error:<br />Query: <tt>'.$sql.'</tt><br />Error: ' . mysql_error($db));

 

Can the above code be improved upon??

 

kind regards

Dazzclub

[Fadion]

In your case mysql_real_escape_string() is enough for sanitizing input. Also there's no need to run it on the password, as it is returned in hash (md5) before the query.

 

In different scenarios ud want to take other measures. If your input data are going to be inserted in the database (as a new row or update) a good way is to run both mysql_real_escape_string() and htmlentities() so u prevent sql injections but cross-site scripting too.

 

Think also of an id parameter passed in the url. U can escape quotes or double quotes after it, but if someone enters an non-existent id it will still break the query. So a good way is to validate input based on its value. For integers u can run intval() or an (int) cast to return an integer and also make error checking, so if that id doesnt exist in the db, show an "page doesnt exist".

 

There's an infinity of cases and scenarios, but the methods are basically the same.

[dazzclub]

Thanks for you detailed reply.

 

kind regards

Dazzclub