Converting to Prepared statements Help

[pahunrepublic]

I am totally new to PHP and prepared statements such as PDO and MySQLi usage. I was told to write codes in these because they are more secure than basic coding. I have the following code but I have problem to convert it to MySQLi prepared statement:

<?php 
include_once 'dbinfo.php';
if(isset($_POST['kuldes']))
{
$name = trim($_POST['nev']);
$username = $_POST['felh_nev'];
$password = $_POST['jelszo'];
$email = $_POST['email'];
$phone = $_POST['telefon'];
$gender = $_POST['sex'];
$hobby = $_POST['hobby'];
$regfelt = $_POST['regfelt'];
$name = strip_tags($name);
$name = stripslashes($name);
$username = strip_tags($username);
$email = strip_tags($email);
$phone = strip_tags($phone);
$date = date("d-m-Y");
if($name == NULL || $username == NULL || $password == NULL || $email == NULL || $phone == NULL || $gender == NULL)
{
echo "Please complete the form below or one of the boxes is empty.";
}
else
{
if(strlen($username) <= 3 || strlen($username) >= 30){
$final_report.="Your username must be between 3 and 30 characters..";
}
else
{
$select_dbase="SELECT * FROM users WHERE username='$username'";
$result=mysqli_query($connect, $select_dbase);
if(mysqli_num_rows($result) != 0){
	$final_report.="The username is already in use!";}
else
{ 
if(strlen($password) <= 6 || strlen($password) >= 12){
$final_report.="Your password must be between 6 and 12 digits and characters..";
}
else
{
if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){ 
$final_report.="Your email address was not valid..";
}
else
{
if(!eregi("^[0-9]{1,3}-[0-9]{1,3}-[0-9]{1,10}$",$phone)){
$final_report.="Phone number is invalid. Only numbers with hyphen. Allowed format: countrycode-areacode-phonenumber";
}
else
{
if(!isset($hobby)){
$final_report.="Youd didn't select any hobbies";
}
else
{
if(!isset($regfelt)){
$final_report.="You didn't accept the terms";
}
else
{
//The implode() function returns a string from the elements of an array.
$h = implode(",", $hobby);
$insert_dbase = 'INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES("' . $name . '","' . $gender . '","' . $email . '",
"' . $phone . '","' . $username . '","' . md5($_POST['jelszo']) . '","'. $h .'")';
mysqli_query($connect,$insert_dbase); 
header("Location: login_form.php");
exit;
}}}}}}}}}
?>
<h1>Registration Form</h1>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="registration_form" method="POST">
<p>Name: <input type="text" name="nev" value="<?php echo (isset($name) ? $name : ''); ''?>" size=25></p>
<p>Username: <input type="text"  name="felh_nev" value="<?php echo (isset($username) ? $username : ''); ?>" size=10></p>
  <p>Password: <input type="password" name="jelszo" size=10></p>
  <!--<p>Password again:<input type="password" name="password_confirmation"></p>-->
    <p>E-mail: <input type="text" name="email" value="<?php echo (isset($email) ? $email : ''); ?>"/></p>
    <p>Phone number: <input type="text" name="telefon" value="<?php echo (isset($phone) ? $phone : ''); ?>"/></p>
<p>Sex: 
       <label><input type="radio" name="sex" value="no" >Female</label>
       <label><input type="radio" name="sex" value="ferfi" >Male</label></p>

<p>Favorite hobbies (Using CTRL you can select more than one):</p>
	<select name="hobby[]" size="4" multiple>
	  <option value="sport">Sport</option>
	  <option value="mozi">Movies</option>
	  <option value="kirandulas">Hiking</option>
	  <option value="olvasas">Reading</option>
  </select>

<!--	<p>Other message:</p>
<textarea name="megjegyzes" cols="40"></textarea>-->

  <p><input name="regfelt" type="checkbox" value="elfogad">I accept the terms!</p>
<p><input name="kuldes" type="submit" value="Submit form">
  <input name="reset" type="reset" value="delete"></p>
<table width="501" border="1">
      <tr>
        <td><?php echo $final_report; ?></td>
      </tr>
    </table>
<p> </p>
</form>

My problems are these parts:

 

the following code:

$select_dbase="SELECT * FROM users WHERE username='$username'";
$result=mysqli_query($connect, $select_dbase);
if(mysqli_num_rows($result) != 0){
	$final_report.="The username is already in use!";}

I converted to this

$select_dbase="SELECT * FROM users WHERE username='$username'";
$select_dbase = $mysqli->real_escape_string($select_dbase);
$result = $mysqli->query($query);
if($result != 0){
	$final_report.="The username is already in use!";}
$result->close();

but without luck, it does not work, it says: Parse error: syntax error, unexpected T_ELSE in

I got stucked and I don't even have the chance to convert this part

$insert_dbase = 'INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES("' . $name . '","' . $gender . '","' . $email . '",
"' . $phone . '","' . $username . '","' . md5($_POST['jelszo']) . '","'. $h .'")';
mysqli_query($connect,$insert_dbase); 
header("Location: login_form.php");
exit;

of the code to prepared statement:

 

Anyone please who can help me?

 

[Maq]

Regarding:

Parse error: syntax error, unexpected T_ELSE in

 

you can't have multiple else's like that.  You should be using elseif's and your final block can be the an else.

[pahunrepublic]

Regarding:

Parse error: syntax error, unexpected T_ELSE in

 

you can't have multiple else's like that.  You should be using elseif's and your final block can be the an else.

Hi Maq

This is what I did:

<?php 
$select_dbase="SELECT * FROM users WHERE username='$username'";
$result=mysqli_query($connect, $select_dbase);
if($name == NULL || $username == NULL || $password == NULL || $email == NULL || $phone == NULL || $gender == NULL)
{
echo "Please complete the form below or one of the boxes is empty.";
}
elseif(strlen($username) <= 3 || strlen($username) >= 30){
$final_report.="Your username must be between 3 and 30 characters..";
}
elseif(mysqli_num_rows($result) != 0){
$final_report.="The username is already in use!";
}
elseif(strlen($password) <= 6 || strlen($password) >= 12){
$final_report.="Your password must be between 6 and 12 digits and characters..";
}
elseif(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){ 
$final_report.="Your email address was not valid..";
}
elseif(!eregi("^[0-9]{1,3}-[0-9]{1,3}-[0-9]{1,10}$",$phone)){
$final_report.="Phone number is invalid. Only numbers with hyphen. Allowed format: countrycode-areacode-phonenumber";
}
elseif(!isset($hobby)){
$final_report.="Youd didn't select any hobbies";
}
elseif(!isset($regfelt)){
$final_report.="You didn't accept the terms";
}
else
{
$h = implode(",", $hobby);
$insert_dbase = 'INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES("' . $name . '","' . $gender . '","' . $email . '",
"' . $phone . '","' . $username . '","' . md5($_POST['jelszo']) . '","'. $h .'")';
mysqli_query($connect,$insert_dbase); 
header("Location: login_form.php");
exit;
}}

 

I had some concerns with the database connection part but it works:

$select_dbase="SELECT * FROM users WHERE username='$username'";
$result=mysqli_query($connect, $select_dbase);

I put it outside elseif tree. Is it going to be a problem?

 

Ok anyway. Going back to the original idea. How would you convert this to prepared statements?

[pahunrepublic]

Hi I se nobody answered me yet. Well meanwhile I tried to convert it to prepared statement:

<?php 
include_once 'dbinfo.php';
if(isset($_POST['kuldes']))
{
$name = trim($_POST['nev']);
$username = $_POST['felh_nev'];
$password = $_POST['jelszo'];
$email = $_POST['email'];
$phone = $_POST['telefon'];
$gender = $_POST['sex'];
$hobby = $_POST['hobby'];
$regfelt = $_POST['regfelt'];
$name = strip_tags($name);
$name = stripslashes($name);
$username = strip_tags($username);
$email = strip_tags($email);
$phone = strip_tags($phone);
$date = date("d-m-Y");
if($name == NULL || $username == NULL || $password == NULL || $email == NULL || $phone == NULL || $gender == NULL)
{
    echo "Please complete the form below or one of the boxes is empty.";
}
elseif(strlen($username) <= 3 || strlen($username) >= 30){
    $final_report.="Your username must be between 3 and 30 characters..";
    }
elseif($stmt = $connect->prepare('SELECT * FROM users WHERE username=?'))
{    $stmt->bind_param('s', $username);
    $stmt->execute();
    $stmt->bind_result($username);
    while ($stmt->fetch())
    {
        printf("Name: %s\n", $name);
        $final_report.="The username is already in use!";
    }
    $stmt->close();
}elseif(strlen($password) <= 6 || strlen($password) >= 12){
    $final_report.="Your password must be between 6 and 12 digits and characters..";
    }
elseif(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){ 
    $final_report.="Your email address was not valid..";
    }
elseif(!eregi("^[0-9]{1,3}-[0-9]{1,3}-[0-9]{1,10}$",$phone)){
    $final_report.="Phone number is invalid. Only numbers with hyphen. Allowed format: countrycode-areacode-phonenumber";
    }
elseif(!isset($hobby)){
    $final_report.="Youd didn't select any hobbies";
    }
elseif(!isset($regfelt)){
    $final_report.="You didn't accept the terms";
    }
else
    {
if ($stmt = $connection->prepare('INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES(?, ?, ?, ?, ?, ?, ?)'))
{
    $stmt->bind_param('sssssss', $name, $sex, $email, $phone_number, $username, $password, $hobby);
    $stmt->execute();
    $stmt->close();
}  
}}?> 
<h1>Registration Form</h1>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="registration_form" method="POST">
<p>Name: <input type="text" name="nev" value="<?php echo (isset($name) ? $name : '');  ''?>" size=25></p>
<p>Username: <input type="text"  name="felh_nev" value="<?php echo (isset($username) ? $username : ''); ?>" size=10></p>
  <p>Password: <input type="password" name="jelszo" size=10></p>
  <!--<p>Password again:<input type="password" name="password_confirmation"></p>-->
    <p>E-mail: <input type="text" name="email" value="<?php echo (isset($email) ? $email : ''); ?>"/></p>
    <p>Phone number: <input type="text" name="telefon" value="<?php echo (isset($phone) ? $phone : ''); ?>"/></p>
<p>Sex: 
       <label><input type="radio" name="sex" value="no" >Female</label>
       <label><input type="radio" name="sex" value="ferfi" >Male</label></p>	
<p>Favorite hobbies (Using CTRL you can select more than one):</p>
	<select name="hobby[]" size="4" multiple>
	  <option value="sport">Sport</option>
	  <option value="mozi">Movies</option>
	  <option value="kirandulas">Hiking</option>
	  <option value="olvasas">Reading</option>
  </select>
  <p><input name="regfelt" type="checkbox" value="elfogad">I accept the terms!</p>
<p><input name="kuldes" type="submit" value="Submit form">
  <input name="reset" type="reset" value="delete"></p>
<table width="501" border="1">
      <tr>
        <td><?php echo $final_report; ?></td>
      </tr>
    </table>
<p> </p>
</form>

and gave me this error: Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement in

[Maq]

Your query has 7 insert values (?) and you give bind 8 parameters.