Specifying parameters for a PHP sql query

Question

I want to specify a raw username/password into an SQL query with PHP:

function doRegister($username, $password) {
    $db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME) or die('error');
    $query = "SELECT username FROM users WHERE username = $username";
    $result = $db->query($query);
    if (mysqli_num_rows($result) == 1) {
        $msg = 'Username already taken';
    } else {
        $register = "INSERT INTO users(username, password)" .
                    "VALUES($username, SHA($password))";

        //error happens here
        $db->query($register) or die('error registering your account');


        $msg = "Register successful";
    }
    $db->close();
    echo $msg;
}

I am getting an error at $db->query($register). What am I doing wrong?

paste the results of mysql command show users; also what mysql error do you get, specifically? — mellamokb
– mellamokb, Commented Nov 16, 2011 at 21:14
@DanielA.White - MAY be? I'd consider that actually susceptible, even if the input parms are (currently) being escaped in the calling method - There's too much of a possibility of a maintainer assuming things are safe elsewhere. Something like this (sepecially like this) should of course use parameterized queries. — Clockwork-Muse
– Clockwork-Muse, Commented Nov 16, 2011 at 23:22

Michael Berkowski · Accepted Answer · 2011-11-16 21:15:06Z

6

You have not surrounded your $username or $password in quotes, so when passing an integer it works correctly but won't accept a string:

$query = "SELECT username FROM users WHERE username = '$username'";
//----------------------------------------------------^^^^^^^^^^^^

$register = "INSERT INTO users(username, password)" .
                    "VALUES('$username', SHA('$password'))";
//-------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Please be certain that you have properly escaped these values with mysql_real_escape_string().

These columns should really not be type TEXT. They ought to be VARCHAR(). Perhaps your VARCHAR() attempt failed because you were missing the length parameter, as in VARCHAR(32) for max 32 characters.

answered Nov 16, 2011 at 21:15

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gen_Eric Over a year ago

Make sure to also quote $username in SELECT username FROM users WHERE username = $username.

Michael Berkowski Over a year ago

@Rocket Good catch, added above.

Aziz Al-ghannam Over a year ago

Actually i didn't miss the length parameter but i thought the problem is with the VARCHAR itself so i moved to TEXT, but you were right about the quotes, problem solved, thanks

gen_Eric · Accepted Answer · 2011-11-16 21:18:24Z

1

Try this:

       function doRegister($username, $password) {
        $db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME) or die('error');
        $query = "SELECT username FROM users WHERE username = '$username'";
        $result = $db->query($query);
        if (mysqli_num_rows($result) == 1) {
            $msg = 'Username already taken';
        } else {
            $register = "INSERT INTO users(username, password)" .
                    "VALUES('$username', SHA('$password'))";
            $db->query($register) or die('error registering your account');
            $msg = "Register successful";
        }
        $db->close();
        echo $msg;
    }

edited Nov 16, 2011 at 21:18

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Nov 16, 2011 at 21:14

Frederick Behrends

3,09526 silver badges47 bronze badges

1 Comment

gen_Eric Over a year ago

You should also quote $username in SELECT username FROM users WHERE username = $username.

Collectives™ on Stack Overflow

Specifying parameters for a PHP sql query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related