2

I have been working on a site in PHP and SQL. In my last question some one pointed out to me that I should be using prepared statements in order to protect my site against SQL injections. I have been going through my site changing all my queries to prepared statements, however I am having a problem with checking if a username exist in the database when a user signs up.

Here is the code that I have tried, it does not produce any errors, however it allows users to sign up with usernames that are already in my users table.

$uname = $_POST['uname'];
$stmt = $link->prepare("SELECT * FROM users WHERE username= ?");

   /* Bind parameters, s - string, b - blob, i - int, etc */
   $stmt -> bind_param("s", $uname);
   $stmt -> execute();

   /* Fetch the value */
   $stmt -> fetch();
   $numberofrows = $stmt->num_rows;

   /* Close statement */
   $stmt -> close();

if($numberofrows>0) {
   echo "Username Already Exist";
} else {
//rest of my code
}

Thanks in advance!

AndrewSturgis19
  • 37
  • 5
  • 1
    You closed your connection too fast. – Funk Forty Niner Nov 26 '19 at 20:57
  • You also need to store results. [See this Q&A](https://stackoverflow.com/q/22252904/1415724) here on Stack. It shows you how to use it completely. – Funk Forty Niner Nov 26 '19 at 20:59
  • 1
    If you don't need more data, only the number of rows, it's better to use `select count(*)` than `select * ` – Felippe Duarte Nov 26 '19 at 21:00
  • Funky Forty Niner Thank you! Is there any way to accept a comment as an answer, and should I put my new code as a answer for other people? – AndrewSturgis19 Nov 26 '19 at 21:02
  • Felippe Duarte I don't understand, should I not use a prepared statement for this? I'm still learning so if you could provide why or why not so I know in the future.. – AndrewSturgis19 Nov 26 '19 at 21:03
  • @AndrewSturgis19 welcome. Btw, you need to use `@member_name` to ping directly. It is a duplicate of it then. I'll just close it as a duplicate :) – Funk Forty Niner Nov 26 '19 at 21:03
  • 1
    Sounds good @FunkFortyNiner :) – AndrewSturgis19 Nov 26 '19 at 21:04
  • 1
    use `count(*)` as suggested by @FelippeDuarte, or if we don't need a count, and we just need to check for existence of a matching row, then we can just do `SELECT username FROM users WHERE username= ? LIMIT 1`, then do the one `fetch` (like in the existing code), and just check if fetch returned a row or not. We see `num_rows` used so many times where its not needed, and there's some variety of behavior with `num_rows`, some of the behavior is unexpected. bottomline: do not use `num_rows`, do a fetch (either of COUNT(*), and test the value returned) or check the fetch of a single row, – spencer7593 Nov 26 '19 at 21:09

0 Answers0