if user_name is already registered, redirect back to the same page ex. register.php?q=user_exists

Question

this the php part

<?php
include 'assets/db.php';

if(isset($_POST["register"])){

    $sql = "INSERT INTO table (name, email, user_name, pass) 

           VALUES ('".mysqli_real_escape_string($conn, $_POST["name"])."',
                   '".mysqli_real_escape_string($conn, $_POST["email"])."', 
                   '".mysqli_real_escape_string($conn, $_POST["user_name"])."', 
                   '".mysqli_real_escape_string($conn, $_POST["pass"])."')";

if ($conn->query($sql) === TRUE) {
    echo "<script type='text/javascript'>window.top.location='_sign_in.php?l=1';</script>";
} else {
    echo "<script type= 'text/javascript'>alert('Error! Please refresh the page and try again.');</script>";
}
$conn->close();

i am trying to avoid re-registration of any user_name, if user_name is already registered, redirect back to some page example- register.php?q=user_exists.

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Feb 23 '18 at 00:31
i see nothing in the code that checks that a user name already exists. — , Feb 23 '18 at 00:31
Are you sure that the INSERT of a duplicate user will actually error? Is someting in there set to unique? — RiggsFolly, Feb 23 '18 at 00:32
___Plain Text Password___ YUK! Use `password_hash()` and `password_verify()` — RiggsFolly, Feb 23 '18 at 00:34
a. set name as unique and check that after making the query, b. a select query for that name first — , Feb 23 '18 at 00:34
@PadraigD well i was not sure if that was the question here or not :-) — , Feb 23 '18 at 00:35
@rtfm If a unique constraint is set there is no need to check for duplicate with a select, just try to insert and if it fails with error 1062, there is a duplicate. — Geoffrey, Feb 23 '18 at 00:54
@Geoffrey a. b. they were 2 options. sometimes you need b as you need the id from the dupe for further processing — , Feb 23 '18 at 00:55

Geoffrey · Accepted Answer · 2018-02-23T01:08:10.003

To avoid duplicate user names, add a unique index to the table:

ALTER TABLE `table` ADD UNIQUE(`user_name`)

This will cause the insert to fail allowing you to detect the failure without needing an additional SELECT to check for a duplicate.

If you want to ensure the error is due to a duplicate value, check for mysql error 1062

Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

You can check for this by using mysqli::errno and then redirect by simply sending the Location header, ie:

if (!$conn->query($sql))
{
  if ($conn->errno == 1062)
  {
    header("Location: register.php?q=user_exists");
    die();
  }

  die('Critical Failure');
}

Edit: Instead of just down voting, how about commenting on what is wrong with this answer?

looks solid to me, not sure why the down votes, you may need to explain how to find the error code — , Feb 23 '18 at 01:03

if user_name is already registered, redirect back to the same page ex. register.php?q=user_exists

1 Answers1