MySQL query not executing properly

Question

I want to make a PHP login script, that when user signs in, it removes the Sign-In form with another div saying "Welcome [user_name]". I am running the script on same page as my html, but the query always fails. Can anyone please sort out this problem, why is this happening?

PHP CODE:

<?php include("connect.php")?>
<?php
    session_start();

    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
        $str = @trim($str);
        if(get_magic_quotes_gpc()) {
            $str = stripslashes($str);
        }
        return mysql_real_escape_string($str);
    }

    if(isset($_POST['username']) && isset($_POST['username'])){

        //Sanitize the POST values
        $UserName = clean($_POST['username']);
        $Password =(md5($_POST['password']));

        //Create query
        $qry = "SELECT 'UserName' , 'Password' FROM users WHERE UserName='$UserName' AND Password='$Password'";
        $result = mysql_query($qry);

        //Check whether the query was successful or not
        if($result) {
            if(mysql_num_rows($result) > 0) {
                //Login Successful
                session_regenerate_id();
                $member = mysql_fetch_assoc($result);
                $_SESSION['SESS_MEMBER_ID'] = $member['mem_id'];
                $_SESSION['SESS_FIRST_NAME'] = $member['FName'];
                $_SESSION['SESS_LAST_NAME'] = $member['LName'];
                //session_write_close();
                echo 'SUCCESS';
                //loggedin();
                //exit();
            }
            else {
                //Login failed
                echo 'FAILED.';
                //loginfail();
                //exit();
                }
            }
        else {
            die("Query failed");
        }   
    }
?>

HTML CODE:

<form name="user-form" id="user-form" action="members.php" method="POST">
                    <input type="text" name="username" id="username" placeholder="Username"></input>
                    <input type="password" name="password" id="password" placeholder="Password"></input>
                    <br/>
                    <input type="submit" id="sign" name="Sign In"></input>
                </form>

Your help will be appreciated as I am new to this.

remove all the single quotes from the column names. `SELECT 'UserName' , 'Password'` should be `SELECT UserName , Password` — Abhik Chakraborty, Jun 12 '14 at 13:08
You should Bind Variables, not sanatize, see here: http://stackoverflow.com/questions/4364686/how-do-i-sanitize-input-with-pdo — Mark, Jun 12 '14 at 13:08
@AbhikChakraborty is correct, you can switch them to ``` backticks if you prefer. — phpisuber01, Jun 12 '14 at 13:09
This question appears to be off-topic because SO is not a helpdesk where we "sort out this problem" for you. — Lightness Races in Orbit, Jun 12 '14 at 13:09
Does your `html` and `php` scripts are in the same `members.php`. If it is, you need to check whether the `$_POST` request called. — Ranjith, Jun 12 '14 at 13:09
Another remark: Please do *not* use md5 for hashing the password. md5 is broken. Additionally these hash algorithms are designed to run fast, which means brute force attacks are more likely to succeed if your database gets compromised. You should use bcrypt (or similar). See also: http://stackoverflow.com/questions/4795385/how-do-you-use-bcrypt-for-hashing-passwords-in-php — dominik, Jun 12 '14 at 13:10
Please be aware that the mysql extension (supplying the mysql_ functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Oldskool, Jun 12 '14 at 13:13

score 0 · Answer 1 · answered Jun 12 '14 at 13:09

This is wrong-

SELECT 'UserName' , 'Password'....

remove ' and replace with

Also take care of the strings properly, just replace WHERE UserName='$UserName' AND Password='$Password'" with-

WHERE UserName=\"".$UserName."\" AND Password=\"".$Password."\""

So the complete query would be-

 "SELECT `UserName` , `Password` FROM users WHERE UserName=\"".$UserName."\" AND Password=\"".$Password."\""

(also keep in mind that the column names are case-sensitive)

score 0 · Answer 2 · answered Jun 12 '14 at 13:09

0

This:

$qry = "SELECT 'UserName' , 'Password' FROM users WHERE UserName='$UserName' AND Password='$Password'";

Should be:

$qry = "SELECT username , password FROM users WHERE UserName='$UserName' AND Password='$Password'";

answered Jun 12 '14 at 13:09

Mark

861
9
17

1

Well, no, you _should_ delimit the column names. But you should do it properly, which means backticks and not single quotes. – Lightness Races in Orbit Jun 12 '14 at 13:09
1

In 12 years of programming I've never worked for a company with standards that involved delimiting column names. This should only ever be necessary if a column name is a reserved word in the SQL database you are using, in which case you should probably be renaming columns. – Mark Jun 12 '14 at 13:13
1

That's fine. But you should point out the bugs which is asked by the questionnaire. This `.....` should be `......`. It's not clearly guide the user. You need to answer make sensible. [Read out here](http://stackoverflow.com/questions/how-to-answer) – Ranjith Jun 12 '14 at 13:20
@Mark: Then you've worked for companies with some pretty awful coding standards, IMO. In 20 years of programming I've never worked for a company which would promote such sloppiness and lack of rigour. – Lightness Races in Orbit Jun 12 '14 at 13:49
I disagree entirely. Coding standards are guidelines at best, and opinion at worst. In this particular case, though, backtick support for column identifiers varies depending on the environment and the environment settings. Developing without them leaves the code flexible enough to support multiple environments without needing substantial modifications, and developing code like that should be habit to allow a developer to more fluidly move between environments. But, that too, is just my opinion. – Mark Jun 12 '14 at 15:10

MySQL query not executing properly

2 Answers2