Help with prepared statements please, select multiple items from DB?

[zoldos]

Below is the start of a new piece code I’m working with. But I want to add some code so it also selects/checks the entered e-mail. How can I do this? I know basically how to set up the bind_param code, but not the prepare statement with multiple items. I hope that makes sense. If anyone can help, I’d appreciate it!

  if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
	// Bind parameters (s = string, i = int, b = blob, etc), in our case the username is a string so we use "s"
	$stmt->bind_param('s', $_POST['username']);
	$stmt->execute();
	// Store the result so we can check if the account exists in the database.
	$stmt->store_result();
  }

[phdr]

check if the account exists in the database

If this is part of a user registration process, go back and read the replies you have already received in your previous threads on this forum.

Summery of those replies - simply define each of the relevant columns in the database table as a unique index, attempt to insert the new data and detect if there is a duplicate key error. If there are multiple columns define as being unique, you would then execute a SELECT query to find which of the value(s) were duplicates.

If you want to match any value(s) in multiple columns, you would add them to the WHERE clause using an OR operator between the expressions - WHERE username = ? OR email = ?

It also appeared in your last thread that you had switched to use the much simpler PDO extension. Why have you now reverted back to the mysqli extension?

[astonecipher]

The way this typically gets done, to prevent race conditions, is to just do the insert. The table has a unique constraint on a column, username would be expected in this case, and then you see if it works. If it throws a duplicate constraint violation, you know that the unique column was violated and let the user know that name is taken.

You also don’t need to actually select columns for determining if the name is taken, just a count on the matched rows.

If you are doing this an ajax call to let the user know as they are putting in the requested username, a count is still what you want.

[zoldos]

I’ve gone back to my custom invite system which was vulnerable to SQL injection and am changing it to prepared statements. Basically it collects information about the user and e-mails this to me for consideration.

I just want a simple way to tell the user if the e-mail or username is taken, which then saves me a headache in the long run.

I’m not actually inserting anything into the DB, just checking it for matching strings.

[zoldos]

The way this typically gets done, to prevent race conditions, is to just do the insert. The table has a unique constraint on a column, username would be expected in this case, and then you see if it works. If it throws a duplicate constraint violation, you know that the unique column was violated and let the user know that name is taken.

You also don’t need to actually select columns for determining if the name is taken, just a count on the matched rows.

If you are doing this an ajax call to let the user know as they are putting in the requested username, a count is still what you want.

I’m not using AJAX, but rather in line errors that display upon clicking submit and return the appropriate error message.

I’m not inserting anything, just comparing input to what is already in the DB and report a dup.

[zoldos]

Okay here’s the updated snippet, it currently produces a blank screen.

 	if ($stmt = $db->prepare('SELECT username, email FROM xf_user WHERE username = ? AND email = ?')) {
	  $stmt->bind_param('ss', $username, $email);
        $stmt->execute();
	  $stmt->store_result();
  	  if($stmt->num_rows>0) {
           $mailuser_error = "<br><span class='error_msg'>Username or e-mail already in use!</span>";
        }else if {  

Help please!

[phdr]

Username or e-mail already in use!

If you want to find if any of the values exist, why did you use AND in the sql query, which would require that a single row contains both values, especially since someone already posted this -

If you want to match any value(s) in multiple columns, you would add them to the WHERE clause using an OR operator between the expressions - WHERE username = ? OR email = ?

[astonecipher]

I’m not inserting anything, just comparing input to what is already in the DB and report a dup

Why do that? If you aren’t inserting them, there wouldn’t can’t be a duplicate anyway. So what is the point in seeing if they are in the table, if you aren’t going to insert them to begin with?

[zoldos]

If you want to find if any of the values exist, why did you use AND in the sql query, which would require that a single row contains both values, especially since someone already posted this -

Okay gotcha.

[zoldos]

Why do that? If you aren’t inserting them, there wouldn’t can’t be a duplicate anyway. So what is the point in seeing if they are in the table, if you aren’t going to insert them to begin with?

It’s an invite system and used to collect info for new users. After I receive the data, I manually create the account. But I’ve found a lot of people either have accounts, or try to use an existing name. The script will check the main database and return errors if the entered info already exists.

Currently tho, it produces a blank page. Should I post the entire script?

[astonecipher]

It’s an invite system and used to collect info for new users. After I receive the data, I manually create the account.

You are creating more work for yourself in that case. Here is what I would recommend,

The form should actually insert into that database. Your side, you would receive a notification email (since that is less work than other things) and you just have a button to actually activate the account rather than inserting everything. How you handle duplicate users is to just not allow them to be inserted in the first place like originally explained, but the notification you receive will let you know that it is someone that was already in the system.

phdr has you on the query itself.

[zoldos]

You are creating more work for yourself in that case. Here is what I would recommend,

The form should actually insert into that database. Your side, you would receive a notification email (since that is less work than other things) and you just have a button to actually activate the account rather than inserting everything. How you handle duplicate users is to just not allow them to be inserted in the first place like originally explained, but the notification you receive will let you know that it is someone that was already in the system.

I already thought about doing that, but I don’t know how passwords are stored in the DB (in fact, I can find no reference at all to passwords anywhere in the DB), so I can’t fully create the account (I need a username, password, and e-mail). So my custom script collects this info (after the person is invited by an existing member), e-mails it to me, and then I create the account. It sounds redundant, but it’s worked thus far, and I’m simply trying to stop the threat of SQL injection…

[astonecipher]

Are you interested in improving the process or just fixing the one piece then?

    $sql = "SELECT username, email FROM xf_user WHERE username = ? AND email = ?";
    if ($stmt = $db->prepare()) { 
        $stmt->bind_param('ss', $username, $email); 
        $stmt->execute();  
        $stmt->store_result(); 
        if($stmt->num_rows>0) { 
            $mailuser_error = "<br><span class='error_msg'>Username or e-mail already in use!</span>"; 
        // }else if { <------------ There is your white screen cause
        // else if requires an expression to evaluate, you aren't providing one.
        }

[zoldos]

Are you interested in improving the process or just fixing the one piece then?

I’d like to streamline the error handling, but the main goal is to convert it to prepared statements and check for existing e-mails and usernames.

[astonecipher]

The automation of creating an account is a very simple crud operation. If you can find no indication of a password field in the database, does it use one or is it required at all for functionality currently?

There are simple things [relatively] you can do for the process. Like, you as the admin manually activates an account. That triggers an email to that users email used to activate their account, think of it like a two part activation process. It is just setting another script up to receive a response from the click of an email.

Error handling can easily be done like you are attempting now. The only issue with that was using else if, when I am not entirely sure you even needed an else.

[zoldos]

I think I’ll give up the project, it’s simply superfluous.

[astonecipher]

Didn’t mean to dissuade you from continuing, rather just show you a more streamlined automatic approach.

[zoldos]

No, you’re fine! The script has just grown out of control and I’d rather focus on other projects. I really do appreciate yours, and everyone else’s time tho!

[zoldos]

I took another look at it, and got it working. hehe Thanks again everyone!