Question about PDO and fixing SQL injection issues for SELECT db code

I know this is more than likely vulnerable. How do I make this into prepared statements/PDO? I reviewed several tutorials and am lost. :frowning:

  $user_check_query = "SELECT * FROM members WHERE member_name='$username' OR member_email='$email' LIMIT 1";
  $result = mysqli_query($db, $user_check_query);
  $user = mysqli_fetch_assoc($result);
  
  if ($user) { // if user exist
    if ($user['member_name'] === $username) {
      array_push($errors, "Username already taken!");
    }

    if ($user['member_email'] === $email) {
      array_push($errors, "E-mail already taken!");
    }
  }

Thanks!

As a simplified example to replace your first 3 lines:-

$user_check_query = "SELECT * FROM members WHERE member_name=? OR member_email=? LIMIT 1";
$sql = $db->prepare($user_check_query) ;
$sql->execute([$username, $email]) ;
$user = $sql->fetch() ;

Note this does not include the PDO connection (where $db is the resulting connection). There you can set a default fetch type, but you may use a different one by including it in the fetch.

3 Likes

Also, are there any restrictions on usernames? For example, could I use my email address as well, or would your code prevent that? If not, what happens when my username is the same as the email address from some other user?

Generally I would advise to either use usernames or email addresses, but not mix them.

2 Likes

As I said in your other thread, do not check for an existing username and/or email. Set a unique constraint on the columns, attempt the insert and capture the duplicate error if any.

If you set a unique constraint on the columns the LIMIT 1 is pointless as there can only be one result anyways.

Stay away from the overhead of calling the array_push function and just add to the array like so…

$error[] = "Your Error Message";
2 Likes

I would as well, but as long as there is a unique constraint on those DB columns it is not a problem.

1 Like

I plan on adding more validation later. I just want to get the password_verify working right now.

Thanks, but I have no idea how to do that. lol

Just read it up from the manual

https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-indexes-keys

1 Like

Okay cool, I’ll check it out!