PHP: Myqli query inside Mysqli query

Question

I'm attempting to make a registration form for my assignments website, it should check if the email address given is already in the database and if its not insert the information and then return the customer id. If it is inside the database it should return the customer id as "exists" which sets of a front end error message. The problem is it seems to be inserting the information before it checks if it exists or not and so every new entry is returning "exists" instead of the users id. My PHP code is below.

<?php
$regemail = $_POST['regemail'];
$regfirst = $_POST['regfirst'];
$reglast = $_POST['reglast'];
$regcontact = $_POST['regcontact'];
$regline1 = $_POST['regline1'];
$regline2 = $_POST['regline2'];
$regline3 = $_POST['regline3'];
$regcity = $_POST['regcity'];
$regcounty = $_POST['regcounty'];
$regpost = $_POST['regpost'];
$regpass = $_POST['regpass'];
$customernumber = "";

//Open a new connection to the MySQL server
$mysqli = new mysqli('127.0.0.1','root','','u221062567_esl');
$results = $mysqli->query("SELECT * FROM customers WHERE `Email Address` = '$regemail'");
if($results ->num_rows > 0){
    $customernumber = "exists";
}
else{
    $regpass = md5($regpass);
    $insertrow = $mysqli->query("INSERT INTO customers(`Email Address`, Password, `First Name`, `Last Name`, `Contact Number`, `Address Line 1`, `Address Line 2`, `Address Line 3`, `City/Town`, County, `Post Code`)VALUES('$regemail', '$regpass', '$regfirst', '$reglast', '$regcontact', '$regline1', '$regline2', '$regline3', '$regcity', '$regcounty', '$regpost');");
    if($insertrow){
        $results2 = $mysqli->query("SELECT * FROM customers WHERE `Email Address` = '$regemail'");
            while($row2 = $results2->fetch_array()) {
            $customernumber = $row2["Customer ID"];
            }
    }
    else{
    }
}
print json_encode($customernumber);

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

// close connection 
$mysqli->close();

?>

Please help as I'm struggling to understand why its running the script in a non-sequential way. Many thanks.

Just in case you don't know, this code is ***very unsafe***. Using MySQLi doesn't magically prevent SQL injection. You need to use prepared statements. Do **not** append (user-entered) variables into your SQL queries. — gen_Eric, Dec 29 '14 at 21:05
*...plus,* MD5 could be the beginning of the end for your site. *Shocking!* — Funk Forty Niner, Dec 29 '14 at 21:06
@JayBlanchard: Oddly, that doesn't seem to be a problem: https://eval.in/238169 — gen_Eric, Dec 29 '14 at 21:07
Try adding in some error checking (`if(!$results){ die($mysqli->error); }`) and debugging (`var_dump($results->num_rows);`). — gen_Eric, Dec 29 '14 at 21:10
Try dumping the variables $results and $results->num_rows to debug and see what is the result of your query, so you can tell if this query is returning the correct values. — Aram Tchekrekjian, Dec 29 '14 at 21:10
Strangely Jay Blanchards answer sorted the problem, I don't know what their bit of code changed but its now working. Also I'm away MD5 is unsafe, I just wanted a simple solution as its only for an assignment, for a real side I'd use crypt but I'd have more time and the back end system wouldnt be so foreign, I'm mainly a front end coder. @RocketHazmat if you could exaplain what you meant a little more I'd be very greatful, I don't know much about SQL injection. — aw555000, Dec 29 '14 at 21:16
That is weird @RocketHazmat. Maybe something with the evaluation of the whole condition when returning `num_rows`? Not in a position to test right now, the space just stuck out to me. — Jay Blanchard, Dec 29 '14 at 21:17
Read this on [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) @aw555000 — Jay Blanchard, Dec 29 '14 at 21:18
@JayBlanchard: It stuck out to me too, but when I made that test, it worked. Maybe because my test was a method call and not a property lookup. — gen_Eric, Dec 29 '14 at 21:21
@aw555000: SQL injection is when I send your script a special value to manipulate your query in ways you don't expect. For example, what if I sent the string `' OR 0=1 -- ` as my `regemail`? What would happen? PHP would replace `$regemail` with what I typed in and your query would be ``SELECT * FROM customers WHERE `Email Address` = '' OR 0=1 -- '``. That's not what you wanted to run, is it? (Note `-- ` is a comment in MySQL). — gen_Eric, Dec 29 '14 at 21:23
@RocketHazmat so maybe change * to email and make some fall back ie if($regemail = ""){give error etc}? — aw555000, Dec 29 '14 at 21:27
@aw555000: `if($regemail = "")` could be helpful. But to prevent SQL injection, you need to use prepared statements. See: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php — gen_Eric, Dec 29 '14 at 21:29
well because the email is passed from a required feild it can never be blank but I have added a space option and will move towards prepared statements, thanks guys — aw555000, Dec 29 '14 at 21:40
try to readbeanphp instead mysqli as it prevents sql inject & build on PDO & easy to start & use — jewelhuq, Dec 29 '14 at 22:15
If in your `if($insertrow...` condition you are looking for the last inserted id you can use `$mysqli->insert_id` instead of doing another query. — Iain, Dec 29 '14 at 22:34
What I've ended up doing is adding an email validator to try and stop it, I will however be adding prepared statements too — aw555000, Dec 29 '14 at 23:22

score 0 · Answer 1 · answered Dec 29 '14 at 22:41

You should be using prepared statements to avoid sql injection, anyways try the below code

function checkEmail($email){
   $stmt = $db->prepare("SELECT email from `customers` where email_address = ?");
   $stmt->bind_param('s', $email);
   $stmt->execute();
   $stmt->store_result();
   if($stmt->num_rows < 1){
      return true;
   }
   return false;
}

function addCustomer($regemail, regfirst, reglast....){
    $stmt = $db->prepare("insert into `customers` (email_address, password, first_name, last_name...)" values(?,?,?,?...));
    $stmt->bind_param('ssss', $regemaail, $hash, $regfirst, $reglast....);
    $stmt->execute();
    if($stmt->afffected_rows > 0){
       return $stmt->insert_id;
    }
    return false;
}

Then call the functions

if(checkEmail($_POST['email'])){
    $customer_number = addCustomer($regemail,...);//this will give you id of the insert, you can do another query using this number I guess since I don't understand why you need it.
    if($customer_number){
        //another query
    }

}
else{
   //email exists
}

PHP: Myqli query inside Mysqli query

1 Answers1