PHP - Prepared Statement Select Fetch

To keep my website more secure, I am currently using Prepared Statement.

The problem is that even the simplest query (e.g. retrieve Member's First Name) involves an INSANE amount of code.

Here is an example of what my code looks like...

// **********************
// Find Member Salt. *
// **********************

// Build query.
$q2 = 'SELECT salt
FROM member
WHERE email=?';

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 's', $email);

// Execute query.
mysqli_stmt_execute($stmt2);

// Store results.
mysqli_stmt_store_result($stmt2);

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt2)==1){
// Member found.
// Bind result-set to variables.
mysqli_stmt_bind_result($stmt2, $salt);

// Fetch record.
mysqli_stmt_fetch($stmt2);

// Close prepared statement.
mysqli_stmt_close($stmt2);
}else{
$salt='';
// $errors['pass'] = 'A System Error has occurred.';
}// End of FIND MEMBER SALT

I am wondering if there is a way to streamline things, perhaps by creating a Function where I can just pass arguments to my specific query needs.  Of course if I do that, then a bad guy could pass arguments to the same Function and possibly doing something really bad?!

Any ideas?

Thanks,


Debbie

P.S.  I am not ready to jump in to OOP, so please keep any responses using old-fashioned Procedural Programming.  Thanks!

After deciding to venture into the realm of prepared statements, I have this line in my script

Quote

if ($stmt = $conn->prepare('SELECT username FROM users WHERE username = ?')) {

Everything was working fine. 

I reviewed my code to adjust it to my old habits, and realized that I had hardcoded the TABLE NAME rather than using a variable.

I updated my code to

Quote

$table = "users";

if ($stmt = $conn->prepare('SELECT username FROM $table WHERE username = ?')) {

and results from my SELECT statement vanished.

Is the use of a variable for a table's name outdated? Even possible??

Hi   I wonder if someone may help, I have already had a good look online, but not found a solution that works with what i'm trying to do   At the moment I have the following code and it works great

$stmt = $mysqli->prepare("SELECT d.id,d.val FROM datalog pd LEFT JOIN datafeeds d ON d.id = pd.did WHERE pd.pid = ? AND pd.aa = ? AND pd.bb = ?");
$stmt->bind_param('sii', $id,$aa=1,$bb=1);
$stmt->execute();
$stmt->bind_result($id,$val);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) != 0)
{
while ($stmt->fetch())
{
$array[] = array('numA'=>$id, 'numB'=>$val);
}
}
return $array;

but what I would like to do is use an array, which could be any length
 

$array = array(1,2,4,7,9);

and change my query to include

d.type IN ($array)

could someone give me some help?   thanks  

Hi All,

I have a prepared statement which give me a bound result $pom

If the value is NULL i would like the output to be "EVEN" if it is not null i would like it to equal itself with a poundsign infront.

I have the following:

$stmt -> bind_result($pom);

	while($stmt -> fetch()){
		$pom = "£{$pom}" ?? "Even";
$out .= "<div>$pom</div>";
}
return $out;

I have found that whatever the outcome, it is the same on each row.

I either get the £ sign or i dont for everything.

If i remove the £ completely to the following, it works perfectly.

$pom = $pom ?? "Even";

 

Edited July 6, 2020 by Adamhumbug

I've decided to move over to using Prepared statements for security purposes, however I'm having problems with the following code.

Any help or suggestions would be appreciated

Output:

Code: [Select]

You are Logged In

Fatal error: Call to a member function bindParam() on a non-object in [b]xxxxxxx[/b]/login.php on line 34


Code:
Code: [Select]
<?php
include "functions.php";
$db_connection = db_connect();
$db_connection2 = db_connect();

$login_statement =  $db_connection->prepare("SELECT COUNT(*) AS accounts FROM `accounts` WHERE `email` = ? AND `password` = ?");
$test_stmt =   $db_connection2->prepare("INSERT INTO `test` (`test`) VALUES (:tst)");



login($_POST[email],$_POST[password],$login_statement);
log_login($test_stmt);

function login($email,$password,$login_statement){

$login_statement->bind_param("ss", $email, $password);
$login_statement->bind_result($accounts);
$login_statement->execute() or die ("Could not execute statement");

while ($login_statement->fetch()) { 

if ($accounts==1){
echo "<br/> You are Logged In <br/>";
}

else{
echo "<br/>Credentials Invalid<br/>";
     } 
}
}

function log_login($test_stmt){

$test_stmt->bindParam(':tst', $tst);  //< ********LINE 34*******
$tst="blah";
$test_stmt->execute()  or die ("Could not execute statement");
}
?>

I wrote a code with prepared statements MySQLi: Code: [Select]
<?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>&nbsp;</p>
</form>And I get the following error message: Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement in
I don't understand what's the problem is, many people can't give solution for this? Anyone who can help me? It's brain racking.

Folks,

Tell me, do you see anything wrong in my INSERT ?

If not, then why is it not INSERTING ? I get no php error, nor mysql error. Button I click. Then form data vanishes as if submitted. I check db and no submission came through!

	<?php
//include('error_reporting.php');
ini_set('error_reporting','E_ALL');//Same as: error_reporting(E_ALL);
ini_set('display_errors','1');
ini_set('display_startup_errors','1');
?>
	<form name = "submit_link" method = "POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<label for="domain">Domain:</label>
<input type="text" name="domain" id="domain" placeholder="Input Domain">
<br>
<label for="domain_email">Domain Email:</label>
<input type="email" name="domain_email" id="domain_email" placeholder="Input Domain Email">
<br>
<label for="url">Url:</label>
<input type="url" name="url" id="url" placeholder="Input Url">
<br>
<label for="link_anchor_text">Link Anchor Text:</label>
<input type="text" name="link_anchor_text" id="link_anchor_text" placeholder="Input Link Anchor Text">
<br>
<textarea rows="10" cols="20">Page Description</textarea>
<br>
<label for="keywords">Keywords:</label>
<input type="text" name="keywords" id="keywords" placeholder="Input Keywords related to Page">
<br>
<button type="submit">Click me</button>
<br>
<input type="reset">
<br>
</form>
	<?php
	if($_SERVER['REQUEST_METHOD'] === 'POST')
{
    /*
    if(ISSET($_POST['submit_link']))
    {*/
        mysqli_report(MYSQLI_REPORT_ALL|MYSQLI_REPORT_STRICT);
        mysqli_connect("localhost","root","","test");
        $conn->set_charset("utf8mb4");
        
        if(mysqli_connect_error())
        {
            echo "Could not connect!" . mysqli_connect_error();
        }
        
        $query = "INSERT into links (domain,domain_email,url,link_anchor_text,page_description,keywords) VALUES (?,?,?,?,?,?)";
	        $stmt = mysqli_stmt_init($conn);
	        if(mysqli_stmt_prepare($stmt,$query))
        {
            mysqli_stmt_bind_param($stmt,'ssssss',$_POST['domain'],$_POST['domain_email'],$_POST['url'],$_POST['link_anchor_text'],$_POST['page_description'],$_POST['keywords']);
            mysqli_stmt_execute($stmt);
            
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
        }
        else
        {
            die("INSERT failed!");
        }
    //}
}
	?>
	

 

@MacGuyver,

 

I will do the VALIDATIONS later. Remember, I was testing myself how much I can code without notes. What you see above was from my memory. Am still a beginner php student for 3yrs now, however!

As for validation stuff will have to check notes and learn or relearn. Meaning, have to memorise the code before I add it here on current project. And so for now, ignore VALIDATIONS and let me know why it's not submitting data into db.

 

HI All,

I am writing a prepared statement to update some user information.

Included in this table are the username and password fields. 

In this particular form, i dont want the user to have access to this information and have built a form that only shows what i want them to be able to change.

The bit that i am not sure about is the prepared statement that i am writing.

I am getting a boolean error suggesting that my prepare failed and i think this may be because i have not named every field in the table.

To give an idea of the table fields i have pulled this from php my_admin (this is not the sql i am running)

UPDATE `ssm_user` SET `user_id`=[value-1],`user_email`=[value-2],`user_password`=[value-3],
`user_firstname`=[value-4],`user_lastname`=[value-5],`user_accountlevel`=[value-6],
`user_mobile`=[value-7],`user_role`=[value-8],`user_lastlogondate`=[value-9] WHERE 1

my prepared statement is

$stmt = $conn->prepare("
			UPDATE ssm_user 
			SET 
			user_email=?,
			user_firstname=?,
			user_lastname=?,
			user_accountlevel=?,
			user_mobile=?,
			WHERE user_id = ?
			");

		$stmt->bind_param('sssssi', $email, $fname, $lname, $accountlevel, $mobile, $uid);
    	$stmt->execute();
    	return $stmt->affected_rows;

Do i have to declare every field in the table or is there something that i am missing here.

I can't get my Updated On timestamp to work in the following query...

Code: [Select]
// ******************************
// Create Temporary Password. *
// ******************************
$tempPass = substr(md5(uniqid(rand(), true)), 3, 10);

// Build query.
$r = "UPDATE member
SET pass=?,
updated_on=?
WHERE email=?
LIMIT 1";

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $r);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 'sss', $tempPass, NOW(), $email);

// Execute query.
mysqli_stmt_execute($stmt2);

I used similar code for an INSERT and it worked fine?!  Now sure what is going on here...


Debbie

Hi All,

I have a select statement

	$stmt = $conn -> prepare('
	                         SELECT u.user_firstname, u.user_lastname, so.staff_id, r.role_name
	                         FROM ssm_staff_order so
	                         INNER JOIN ssm_user u on so.staff_id = u.user_id
	                         INNER JOIN ssm_role r on u.user_role_id = r.role_id
	                         WHERE job_id = ?
	                         ');
	$stmt -> bind_param('i', $jid);
	$stmt -> execute();
	$stmt -> bind_result($fn, $ln, $id, $role);

The result looks like this

+----------------+---------------+----------+-----------+
| user_firstname | user_lastname | staff_id | role_name |
+----------------+---------------+----------+-----------+
| FName          | LName         |        8 | Chef      |
| jon            | smith         |       15 | Manager   |
| Chelsea        | Hockley       |        2 | Manager   |
+----------------+---------------+----------+-----------+

I am wanting to build an array so that i can foreach the result with the people under the header of their role.

I know this is not difficult but i am going around and around with it and clearly missing the key part.

As always your help is appreciated.

Hi,

I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense.

However, I need to know how the join should be implemented with MySQLi prepared statement.

I need some one to review the following statement for me and advise:

if ($stmt = $mysqli->prepare("Select t_persons.PersonID    
                                                                    
        ,t_persons.FamilyName
        ,t_persons.FirstName
        ,t_persons.OtherNames
        ,t_persons.Gender
        ,t_persons.CountryID
        ,t_persons.ImagePath    

        ,t_incidents.IncidentID
        ,t_incidents.Incident
        ,t_incidents.IncidentDate
        ,t_incidents.PersonID
        ,t_incidents.CountryID
        ,t_incidents.OffenceKeywordID
        ,t_incidents.StatusID
        ,t_incidents.AgencyID

        ,t_status.StatusID
        ,t_status.Status

        ,t_offencekeyword.KeywordID
        ,t_offencekeyword.Keyword

        ,t_countries.CountryID
        ,t_countries.Country

        ,t_agencies.AgencyID
        ,t_agencies.Agency

From t_persons
    Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
    Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
    Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
    Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
    Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID

Where t_persons.PersonID = '$PersonID'")) {

Regards.

josephbupe

i have a table of say 100 entries.  i want to delete all but the 10 newest of those rows with a prepared statement.  im fairly new with prepared statements so the syntax with a subquery is throwing me a bit.

$stmt = $db_connect->prepare('DELETE FROM table WHERE owner=? AND owner NOT IN (SELECT owner FROM table ORDER BY dob DESC LIMIT 10)');
$stmt->bind_param('i', 0);
$stmt->execute();
$stmt->close();

Hi, Im just having some trouble with this...maybe a fresh pair of eyes can help? 

Im getting a "Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables" error when I try run this:

Code: [Select]
$date = date("Y-m-d");
        $header = $_POST['header'];
        $summary = $_POST['summary'];
        $content = $_POST['content'];
 
        $query = "INSERT INTO articles (pubdate, title, summary, content) VALUES(?, ?, ?, ?)";
        $stmt = $mysqli->stmt_init();
       
        if ($stmt->prepare($query)){
           
            $stmt->bind_param('i,s,s,s', $date, $header, $summary, $content);
 
            $stmt->execute();
            $stmt->close();
        }
        else {
            echo "ERROR: SQL statement failure!";
            echo "<a href='addnews.php'> -> OK</a>";
        }
        $mysqli->close();

It looks fine to me, just can't see whats wrong lol!

Need some help here!

I want to build a friendgroup - I have a full working friendsystem.

My ide is this. I got a list of friends, make a group named Test. Put selected friends in there, make another group and put another friends in there.

Some idees how to do that with prepared statements??

I search for tutorials, but couldn't find any. Any suggestions?

The functions I need is this:

1. Make group
2. Add friend to group
3. Show group with selected friends
4. Rename groups
5. Move friend from one group to another
6. Delete group with all friends inside
7. Delete a empty group
8. Option to search for friends inside a group

Tutorial, free source code - everything would help to solve this mystery !

So far is this the code I made, and didn't work at all

public function create_friendgroup($profileownerid, $friends = NULL, $name)
{

   $sql = "SELECT fg_friends_id FROM friend_groups WHERE fg_member_id = '$profileownerid' LIMIT 1";

      if($stmt = $this->conn->prepare($sql))
      {
         $stmt->execute();
         $stmt->bind_result($friend);
         $stmt->fetch();
         $stmt->close();

      }

   if($friend != null)
      {
         $ua = explode(",", $friend);
         $ua = array_unique($ua);

         foreach ($ua as $u)
         {
         if($u !=NULL && $u !=$profileownerid)
         {
         $oldusers .= "{$u},";
      }   }
   }

$newusers = $profileownerid;
      if ($oldusers != null)
         {
         $newusers .= "," . $oldusers;
         }

$sql = "INSERT INTO friend_groups(fg_member_id,fg_friends_id, fg_name, fg_created_date) VALUES (?,?,?,?)";

$date = date("d-m-Y H:i");
      
      if($stmt = $this->conn->prepare($sql))
      {
         $stmt->bind_param('iiss',$profileownerid,$friends = trim($newusers, ","), $name,$date);
         $stmt->execute();
         $stmt->close();
      }
}







K.F

Hi,   Does anyone have a clue how I might solve this little issue:   I have a MySQL query, for example:

<?php
// Make a MySQL Connection
$query = "SELECT * FROM staff";
    
$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){

    if($row['clock'] < time()) {
    echo "<b>"$row['name']. " - ". $row['age']"</b>";
    echo "<br />";

    } else {

    echo $row['name']. " - ". $row['age'];
    echo "<br />";

    }
}
?>

Taking data from the following table setup:   name - age - clock Timmy Mellowman - 23 - 09:00:00
Sandy Smith - 21 - 12:00:00
Bobby Wallace - 15 - 14:00:00   What im trying to achieve is compare the current time to the time in the clock column and depending if it's earlier or later, change the styling of the results.   The above code does appear to work somewhat however it seems to change the styling of all results in one go, rather than individually when it passes the time in the cell, which is what im looking for.   Thanks