PHP registration script - Username check does not work

[as4s1n]

I wrote a script for a registration page and all of a sudden it just stopped working. Whenever I try something it either reads "Username is taken" even when it isn't and if it doesn't show anything I get either an error (on a separate file) that tells me "Invalid parameters, incorrect number of tokens" or "Unknown column $name in where clause" the $name being replaced by the value. I am completely lost.

$info = array(
":name"=>$_POST['name'],
":pass"=>md5($_POST['password']),
":email"=>$_POST['email'],
":gender"=>$_POST['gender'],
":age"=>$_POST['age'],
":location"=>$_POST['location'],
":avatar"=>$_FILES['avatar']['name'],
":avatarSize"=>$_FILES['avatar']['size']); 
# ...
 
 try {
$sth = $dbh->prepare("SELECT username as numrows FROM users WHERE username = :name");
$sth->execute($info);
} catch(PDOException $e) {
writeError($e->getMessage(),CURPAGE);
}
if($sth->rowcount() > 0)
 $error[] = "<b>Username is taken</b> "; 

Please help.

[lemon-tree]

Try getting rid of 'as numrows'. I don't use think you can insert your :name variable like that either, but I may be wrong.

[as4s1n]

Still the same problem. Misson always tells me to do it like that but just to be safe I changed it to this:

$sth = $dbh->prepare("SELECT id FROM users WHERE username = ?");
$sth->execute(array($_POST['name'])); 

Still the same problem

[Hello71]

Isn't the correct code

$sth->execute("s", array($_POST['name'])); 

?

[misson]

Whenever I try something it either reads "Username is taken" even when it isn't and if it doesn't show anything I get either an error [...] or "Unknown column $name in where clause" the $name being replaced by the value. I am completely lost.

Print the statement (with PDOStatement::debugDumpParams) and the parameter values. Try the query in phpMyAdmin. Also print the results of PDOStatement::errorInfo.

and if it doesn't show anything I get either an error (on a separate file) that tells me "Invalid parameters, incorrect number of tokens"

PDOStatement::execute doesn't like getting more values than there are parameters. That's probably the source of this error. In this instance, using a positional parameter is fine. If there are many parameters and the input parameter array is assembled elsewhere, you can use array_intersect_key to extract only the parameters the query needs.

Misson always tells me to do it like that

Not always. For queries with just a few arguments where the parameter array is constructed near the definition of the statement, positional parameters are fine. Named parameters are for when there are numerous parameters (so you can keep them straight) or when the input parameter array is assembled far from the statement, so that you can't readily look at the query and see the parameter order.

Isn't the correct code

$sth->execute("s", array($_POST['name'])); 

?

PDOStatement::execute doesn't need to be told types for the parameters. You might be thinking of mysqli::bind_param.

[as4s1n]

Here is what I get if I use my first section of code:

SQL: [45] SELECT id FROM users WHERE username = ':name' Params: 8 Key: Name: [5] :name paramno=-1 name=[5] ":name" is_param=1 param_type=2 Key: Name: [5] :pass paramno=-1 name=[5] ":pass" is_param=1 param_type=2 Key: Name: [6] :email paramno=-1 name=[6] ":email" is_param=1 param_type=2 Key: Name: [7] :gender paramno=-1 name=[7] ":gender" is_param=1 param_type=2 Key: Name: [4] :age paramno=-1 name=[4] ":age" is_param=1 param_type=2 Key: Name: [9] :location paramno=-1 name=[9] ":location" is_param=1 param_type=2 Key: Name: [7] :avatar paramno=-1 name=[7] ":avatar" is_param=1 param_type=2 Key: Name: [11] :avatarSize paramno=-1 name=[11] ":avatarSize" is_param=1 param_type=2

And if I use the second set:

SQL: [39] SELECT id FROM users WHERE username = ? Params: 1 Key: Position #0: paramno=0 name=[0] "" is_param=1 param_type=2

@Misson: If I used the array_intersect_key() method you talked about, which two arrays would I be comparing? Would it be something like:

$sth->prepare("... :name");
$sth->execute(array_intersect_key($info,something)); 

Also, was that the right way to do the :key? Or do I have to place ' 's around it to turn it into a string?

I.E.:

$sth = $dbh->prepare("... WHERE username = :name"); 

Edit 1: I changed it a little bit, now I get a blank screen when I try.

$info = array(
":name"=>$_POST['name'],
":pass"=>md5($_POST['password']),
":email"=>$_POST['email'],
":gender"=>$_POST['gender'],
":age"=>$_POST['age'],
":location"=>$_POST['location'],
":avatar"=>$_FILES['avatar']['name'],
":avatarSize"=>$_FILES['avatar']['size']);

#...
 try {
$sth = $dbh->prepare("SELECT username FROM users WHERE username = ?");
$sth->execute(array($_POST['name']));
} catch(PDOException $e) {
$sth->errorInfo();
$sth->debugDumpParams();
writeError($e->getMessage(),CURPAGE);
}

if($sth->rowcount() > 0)
    $error[] = "<b>Username is taken</b> "; 

[docttor]

$info = array( 
$name = $_POST['name'], 
$pass = md5($_POST['password']), 
$email = $_POST['email'], 
$gender = $_POST['gender'], 
$age = $_POST['age'], 
$location = $_POST['location'], 
$avatar = $_FILES['avatar']['name'], 
$avatarSize = $_FILES['avatar']['size']); 


 try { 
$sth = $dbh->prepare("SELECT username FROM users WHERE username = '{$name}' "); 
$sth->execute(array($_POST['name'])); 
} catch(PDOException $e) { 
$sth->errorInfo(); 
$sth->debugDumpParams(); 
writeError($e->getMessage(),CURPAGE); 
} 

if($sth->rowcount() > 0) 
    $error[] = "<b>Username is taken</b> "; 

Maybe now will work

[as4s1n]

Thanks, it works now... Now can you explain how that works? You defined a variable inside the array variable. Is that correct. And why do you need to use {$name} for that? Also, how do I call those variables onto the script? Is it $info['$name'] or something?

[misson]

Here is what I get if I use my first section of code:

SQL: [45] SELECT id FROM users WHERE username = ':name' Params: 8

From this, we see that you're quoting the parameter (don't) and passing too many parameter values. One is defined, you pass eight.

@Misson: If I used the array_intersect_key() method you talked about, which two arrays would I be comparing?

You should be able to figure this one out. You only want certain entries from the input array, those that are named parameters. Thus you intersect the input array and an array defining which keys:

    $sth->execute(array(array_intersect_key($info, array(':name' => null))); 

However, in this case, using a single positional parameter is simple and clear, so that's what you should do.

Another alternative (not to be used in this case) is to use PDOStatement::bindParam or PDOStatement::bindValue, then call execute() with no arguments. bindParam is particularly useful when executing the query multiple times and the value is always stored in the same variable; you can bind the parameter once and simply change what's stored in the variable.

Also, was that the right way to do the :key? Or do I have to place ' 's around it to turn it into a string?

Parameters are unquoted, just like variables are unquoted in other languages (you wouldn't write strtolower('$action'), would you?). Again, the documentation covers this; take a closer look at it. Remember, the point of using quotes when you interpolate a value is to delineate the embedded data, to separate it from the host language.

Edit 1: I changed it a little bit, now I get a blank screen when I try.

The fragment you posted only outputs something when there's an error. Perhaps the blank screen is an indication that the query succeeded, and the user name is available. Print something (e.g. "Username $info[:name] is available.") on success so you can tell.

The call to PDOStatement::rowCount should be within the try block. If an exception is thrown, $sth won't hold valid results, and any calls on it that deal with results are meaningless. You need to consider what's valid and what isn't when thinking about errors.

$sth = $dbh->prepare("SELECT username FROM users WHERE username = '{$name}' "); 

One of the two main points behind prepared statements is to avoid doing just this, in order to prevent SQL injection. You need to study more.

You defined a variable inside the array variable. Is that correct. [...] Also, how do I call those variables onto the script? Is it $info['$name'] or something?

$name = 'value' is an expression (i.e. it has a value), distinguished from statements (e.g. if statements), which don't have values (read "Expressions vs Statements" and "Expression Versus Statement" for more). It's the value of the expression that gets stored in the array, where indices get assigned as all integer indices are assigned. You'd refer to it as $info[0].

Docttor uses in the same way as:

$db->prepare($stmt = "SELECT ...");

// Note: assignment, not comparison
while (($row = $query->fetch())) {
    ...
}
/* Note this is better written as:
    foreach ($query->fetch() as $row) {
but it serves to illustrate the use of assignment expressions.
*/ 

And why do you need to use {$name} for that?

The braces are for interpolating "complex" expressions, such as $foo->bar->baz(). In this case they're not necessary, as $name isn't a "complex" expression, but they do make it stand out more in the string.

[as4s1n]

You should be able to figure this one out. You only want certain entries from the input array, those that are named parameters. Thus you intersect the input array and an array defining which keys:

    $sth->execute(array(array_intersect_key($info, array(':name' => null))); 

That would probably be more than I would want to write, I would simplify it to this

$sth->execute(array($_POST['name'])); 

Thanks for the help, it finally works without flaw.