Mysqli - Can someone cast an eye over these statements?

[Bottyz]

Hi all,

 

I've just started upgrading from procedural style mysql statements to object orientated style mysqli prepared statements. I was just hoping someone could look over my registration page statement code and tell me if there are any bits that are unecessary or a security issue (All of the code does already work - just want it checking)

 

The reason I would like them checked over is because I've never touched OOP or mysqli in my life as I haven't been doing php all that long. I've had my own mysql statements running great for a couple of years but I want to keep with the times! If they are ok, then I'll go on to convert the remainder of the site

 

First of all I include the mysqli database connection script before each mysqli statements (as php auto drops the connection once the script has finished - a little annoying as it increases the code. But I can see the resources advantages.  ):

 

../db/dbauth.php (outsite www root):

	$mysqli = new mysqli($hostname_lh, $username_lh, $password_lh, $database_lh);

	if (mysqli_connect_errno()) {
		$mess = "There was a mysqli connection error! Mysqli Error: " . mysqli_connect_error();
		$contact_email = "webmaster@website.com";
		$message_sub = "Mysqli Connection Error";
		$hdrs = "From: " . $contact_email . "\r\n";
		$hdrs .= "Reply-To: ". $contact_email . "\r\n";
		$hdrs .= "MIME-Version: 1.0\r\n";
		$hdrs .= "Content-Type: text/html; charset=UTF-8\r\n";
		mail($contact_email, $message_sub, $mess, $hdrs);
		exit();
	}

 

The above will send an email if there are any connection problems. This file has to be called before each mysqli statement... is there an easier way of doing this? I don't want to have it in my file as it contains the password/database details. Do I have to include the file each time I use mysqli in a file as I have been doing? Some files contain upto 4-5 mysqli statements, so i'm effectively including the dbauth.php file 4-5 times.

 

Anyways to the statements:

 

First prepared statement is for populating a countries drop down box:

// connect to db for mysqli
require_once('../db/dbauth.php');

// populates countries drop down
$countrystmt = $mysqli->stmt_init();
if ($countrystmt = $mysqli->prepare("SELECT country FROM countries")){
$countrystmt->execute();
$countrystmt->bind_result($option);
while ($countrystmt->fetch()) {
	// records stored in $option
	echo "<option value='" . $option . "'";
	if ($user_country == $option) {
		echo " selected";
	}
	echo ">" . $option . "</option>";
}
$countrystmt->close();
}
$mysqli->close();

 

This one checks the db to see if the user already exists:

	// connect to db for mysqli
	require_once('../db/dbauth.php');

	// checks if the username is in use
	$result = $mysqli->stmt_init();
	if ($result->prepare("SELECT their_username FROM users WHERE their_username=?")){
		$result->bind_param("s", $their_username);
		$result->execute();
		$result->store_result();
		$row_count = $result->num_rows;
		$result->close();
	}
	// if query errors sends an email
	$mysqli->close();		

	// if the name exists it gives an error
	if ($row_count != NULL) { //..... .... .... 

 

 

next one inserts a new user into the db (after some santitising of inputs):

	// connect to db for mysqli
	require_once('../db/dbauth.php');

	// inserts a new user
	$null = NULL;
	$insert_stmt = $mysqli->stmt_init();
	if ($insert_stmt->prepare("INSERT INTO members VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
		$insert_stmt->bind_param('issssssssssssiiiiisiis', $null, $user_name, $user_companyname, $user_email, $user_1stline, $user_address2, $user_town, $user_county, $user_postcode, $user_country, $user_tel, $their_username, $null, $zero, $zero, $zero, $zero, $zero, $user_serial, $zero, $zero, $zero);
		$insert_stmt->execute();
		$insert_stmt->close();
	}

	// if query errors sends an email
	if ($mysqli->error) {
		try {   
			throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno);   
		} catch(Exception $e ) {
			$mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >";
			$mess .= nl2br($e->getTraceAsString());
			$contact_email = "webmaster@website.com";
			$message_sub = "Mysqli Registration Query Error [uAU01]";
			$hdrs = "From: " . $contact_email . "\r\n";
			$hdrs .= "Reply-To: ". $contact_email . "\r\n";
			$hdrs .= "MIME-Version: 1.0\r\n";
			$hdrs .= "Content-Type: text/html; charset=UTF-8\r\n";
			mail($contact_email, $message_sub, $mess, $hdrs);
		}
		header("refresh: 10; registration.php");
		die('ERROR: Unable to add you as a new user. Please report this error to us using our contact us form.<br><br>We will redirect you back to the registration form in 10 seconds.<br><br><div style="margin: auto; text-align: center;"><img src="http://static.website.com/images/loading.gif" alt="loading"></div>');
		exit();
	}		
	$mysqli->close();

 

If the script above errors then it will send an error email to the webmaster (me) and also give a friendly error to the user before redirecting back to a registration page.

 

I've got a similar error and refresh method for the other statements further up, but didn't think I should overload everyone with loads of similar code! 

 

Anyways,  muchos help always appreciated!!! :)