PHP - Simple Prepared Statements Question

Heya, i'm a noob looking for some criticism on a bit of code that I wrote. I'm trying to form a function for updating records within a database. I've just recently learned about prepared statements so i'm trying to implement them into my existing code for security reasons. The problem that i'm having with using a function is that I have to pass the table names through variables which can't be used as a parameter for the PS. Therefore leaving me with the question: should I be using a function for this or should I just have multiple PS's throughout my code? Below is what I currently have running.

Throughout my code I have arrays generated by forms:

if( isset($_POST['edit']) )
{ 

foreach( $_POST as $var => $value )

{

$fields[] = $var;

$vars[] = $value;

}
}

Then I send the arrays to the function:

update( database,

table,

$fields,

$vars,

field,

null,

null,

$field,

null,

null,

'ORDER BY id',

'LIMIT 1',

1 );

Function:

function update( $database, $table, $fields, $vars, $field1, $field2, $field3, $value1, $value2, $value3, $order, $limit, $num )
{
    $database = database( $database );
    $table = table( $table );
    $order = order( $order );
    $limit = limit( $limit );
    $num = intval( $num );

    $db = new mysqli( 'localhost', '*', '*', $database );
    $stmt = $db->stmt_init();
    $arraySize = count( $fields );

    for( $int = 0; $int < $arraySize; $int++ )
    {
            if( checkInt( $fields[$int], $vars[$int] ) )
            {
                $vars[$int] = intval( $vars[$int] );
                $par1 = "i";
            }

            else
            {
                $par1 = "s";
            }

            if( $num == 1 )
            {
                if( checkInt( $field1, $value1 ) )
                {
                    $value1 = intval( $value1 );
                    $par2 = "i";
                }

                else
                {
                    $par2 = "s";
                }

                if( $vars[$int] != 'Submit' )
                {
                    $stmt->prepare( "UPDATE $table SET
                        $fields[$int] = ?
                        WHERE $field1 = ?
                        $order
                        $limit" );

                    $stmt->bind_param( "$par1$par2", $vars[$int], $value1 );
                    $stmt->execute();
                }

    }
    }
    $stmt->close();
    $db->close();
}

I was told to hardcode all of the variables not being prepared. I'm not positive this is actually helpful though. Any input here would be appreciated. (i've done this for $database, $table, $order, $limit, $num:

function table( $value )
{

if( empty( $value ) )

{

break;

}

else

{

$tables = array(

'comments',

'results' );

$key = array_search( $value, $tables );

return $tables[$key];

}
}

I guess i'm asking if this is OK or am I heading down the wrong direction. I hear using classes is a better solucion but i'm having trouble trying to understand how to use them.

Any help would be appreciated, thanks!

Hello there

 

I'm really struggeling with my prepared statement. I want to get the number of unread PMs to show at the user's homepage. Can someone help me with some explanation about my code? I don't really know what I'm doing yet. I've read a lot of code about prepared statements, but I still can't figure it out. 

public function count_unread_pm() {
        if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1")) {

            /* Bind parameters, s - string, b - blob, i - int, etc */
			$stmt->bindParam(':unread', $id, PDO::PARAM_INT); 
            $stmt -> execute();

            var_dump($stmt); // no output 

            /* Bind results */
            $stmt -> bind_result($test); // Error: call to undefined method 

            /* Fetch the value */
            $stmt -> fetch();
            $numberofrows = $stmt->num_rows;

            /* Close statement */
            $stmt -> close();
        }
        var_dump($numberofrows); // no output 

    }

Edited September 16, 2019 by Fabel

in php, pdo, the code does not output any error messages when table is not found.

try {
$stmt = $dbh->prepare("SELECT * FROM 1234"); 
$stmt->execute(); 
$row = $stmt->fetch();
	
} catch (PDOException $e) {
  echo $e->getMessage().' in '.$e->getFile().' on line '.$e->getLine();
}

the code only seems to work when the following code is placed just under the "try {"

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

the above code is used just below the connection to the database code. how to get the try - catch code to work without using the setAttribute code every time for prepared statements?

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:
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.";
}
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>&nbsp;</p>
</form>My problems are these parts:

the following code:
Code: [Select]
$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
Code: [Select]
$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 Code: [Select]
$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?

class getjson {
function query_db ($mysqli, $search_string){
    $query = "SELECT name, location, address FROM csv_table WHERE address LIKE CONCAT('%',?,'%')
    OR name LIKE CONCAT('%',?,'%')
    OR location LIKE CONCAT('%',?,'%') LIMIT 0,5";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("sss", $search_string, $search_string, $search_string);
    $stmt->execute();
//    $stmt->bind_result($name, $location, $address); // not necessary for code to work
    $result = $stmt->get_result();
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
        foreach ($row as $value)
          {
                    $data[] = array(
                    'label' => $value .', '. $value.' '. $value ,
                    'value' => $value );
            }

        }
                return $data;
    }
}
$results = new getjson;
$results = $results->query_db($mysqli, $search_string);
print_r($results);
echo count($results);     I have the following code. It works, but I cannot get array notation working correctly. For instance, I would like to access $row['address'] to input into my array in the foreach statement. But I can't seem to get it to work after a couple hours. So any ideas appreciated.   Thanks, Mark
 

 PDO-Prepared Statements using mysqli_real_escape_string Is it a good Idea to use mysqli_real_escape_string  for extra  security  In the Prepared Statements

<?php

	try {
      
	  require_once '../includes/pdo_connect.php';
      $make = mysqli_real_escape_string($_GET['make']);
      $sql = 'SELECT *
                FROM cars
                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price
                ORDER BY price';
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':make', '%' . $make . '%');
        $stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);
        $stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);
        $stmt->execute();
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }

In lieu of prepared statements, will this work effectively?  Is it overkill?

$username = mysqli_real_escape_string($conn,$_POST["username"]);

	$username = strip_tags(trim($username));

Can I write it this way:

$username = mysqli_real_escape_string($conn,strip_tags(trim($_POST["username"])));

Are prepared statements a guarantee for defeating an injection attack?  Or should they be used in conjunction with the above (or other) coding to bolster a database's defenses?

I have just got hold of a MySQLi class (a wrapper to the built in one of course), and for EVERY query sent they have used prepared statements.

Is this right? I expected it to just send using the mysqli_query function (with the prepared statements option if selected).

Should you send ALL queries using a prepared statement?

What (if any) are the downfalls of using prepared statements?

Hi, how do I use prepared statements when working with classes and functions? I need to do perform multiple identical inserts/updates to mySQL. I have been trying to implement into my connection class, based on what I have seen in examples I found on google. I keep getting fatal errors.

My code is (save.php)

<?php
//Class extends DB_connect to access database
class saveWorkout extends DB_Connect {
  // $stml->close();
  public function get_jQueryData($planned_workout_id,$array_input) {
    // loop through workout progress, and check if input already exists in database
    for ($i = 0; $i < count($array_input['field_id']); $i++) {
      if(empty($array_input['field_id'][$i])) {
        $planned_workout_id = $array_input['planned_id'][$i];
        $exercise_id = $array_input['exercise_id'][$i];
        $set_id = $array_input['set_id'][$i];
        $weight = $array_input['weight'][$i];
        $reps = $array_input['reps'][$i];
        $this->insertNewEntry->$stmt->execute();
      } // closing else
      else {
        // Do something
      } //closing else
    } // Closing for statement
  }

  protected function insertNewEntry() {
    $stmt = $this->$connect()->prepare("INSERT INTO Workout_Log (planned_workout_id, exercise_id, set_id, weight, reps, entry_date) VALUES ( ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param($planned_workout_id, $exercise_id, $set_id, $weight, $reps, date("Y-m-d H:i:s"));
  }
} // closing class save_workout
?>

my connection file is looks like this (connect.php)

class DB_Connect {
  private $servername;
  private $username;
  private $password;
  private $dbname;

  protected function connect() {
    $this->servername = "localhost";
    $this->username = "root";
    $this->password = "XXXXXX";
    $this->dbname = "NNNNNNN";
    $conn = new mysqli($this->servername,$this->username,$this->password,$this->dbname);
    return $conn;
  }
};

 

Edited March 25, 2020 by Stoffer

I'm trying to work with prepared statements, but unfortunately I do not get any result back. I also tried while($stmt->fetch()) { ... }, with the same effect.    Does anyone have a suggestion?   Thanks in advance.

<?php
MAKING CONNECTION

if ($stmt = $mysqli->prepare("SELECT year FROM SOMETABLE WHERE id = ? LIMIT 1")) {

    $id = $_GET['id'];
    
    $stmt->bind_param("i", $id);
    
    
    $stmt->execute();
    
    
    $stmt->bind_result($year);
    
    $stmt->fetch();
    
    echo $year;
        
    
    $stmt->close();
}
$mysqli->close();

?>

Ok.  So I just started using prepared statements.  One issue I ran into is that after inserting say "abc's" into the table with a prepared statement when I read that row and display it, it shows as abc\'s 

I have to use stripslashes on the variable before displaying it. 

I thought that with magic quotes. off this would not be a problem. Am I going to have to strip slashes on all fields now? Is there another way around it?

here is my phpinfo for reference

magic_quotes_gpc   Off   Off
magic_quotes_runtime   Off   Off
magic_quotes_sybase   Off   Off

And compile options

Configure Command    './configure' '--host=i686-redhat-linux-gnu' '--build=i686-redhat-linux-gnu' '--target=i386-redhat-linux' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-expat-dir=/usr' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-kerberos' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--without-mime-magic' '--without-sqlite' '--with-libxml-dir=/usr' '--with-xml' '--with-mhash=shared' '--with-mcrypt=shared' '--with-apxs2=/usr/sbin/apxs' '--without-mysql' '--without-gd' '--without-odbc' '--disable-dom' '--disable-dba' '--without-unixODBC' '--disable-pdo' '--disable-xmlreader' '--disable-xmlwriter' '--disable-json'

Thanks

JT

The first piece of code, "screenshot_1", works perfectly but of course is open to SQL injection. If I use prepared statements as per "screenshot_2", and change $result from $con->query($query) to $result= $query->execute()it doesn't work and gives the error, "Fatal error: Uncaught Error: Call to a member function fetch() on bool ". I have read somewhere that this is caused because the 'Select' statement has failed.I am amazed that three lines of code (copied from a php manual) has caused the whole issue to fail. Where have I gone wrong?

,  

Hi

I'm have a PHP form that submits to a MYSQL database and I'm trying to make it so when any form input field is empty the user gets a message below that field and the data isn't submitted. I can't seem to get it work though?

The data submits OK when I don't have any validation but obviously if the fields are empty then a blank record just gets added to the database.

 

    <?php 
      
        // submit data to database
        if (isset($_POST['submit'])) {

            // assign variable names to name attributes from the form
            $firstname = $_POST['first-name'];
            $email = $_POST['email'];
			
			// This is the attempted validation code
            if(empty($firstname)) {
                $error= "<br>- Please enter your firstname";
            }
            if(empty($email)) {
                $error="<br>- Please enter your email";
            }

            if ($error) {
                $result="<p class='alert error'>There is an error. Please correct the following: {$error}</p>";
            } else {
		
            // This is when the data is submitted
            $query = "INSERT INTO users(firstname, email) VALUES(?,?)";

            $stmt = $connection->prepare($query);
            $stmt->bind_param("ss", $f, $e);

            $f = $firstname;
            $e = $email;
            $stmt->execute();

            $stmt->close();
            $connection->close();

            header("Location: index.php"); // makes page refresh after query so new records show in HTML table

        }
        
    }
    ?>

 

Hello,

I am currently trying to learn php from a book called PHP for Absolute Beginners where it teaches you how to build a blog by "hand". All nice and good until i got to the submit an entry and save it to my database process.
I'm using PDO with INSERT prepared statement like so, from a separate update.inc.php file:

Code: [Select]
$sql = "INSERT INTO entries (title, entry) VALUES (?,?)";
$stmt = $db->prepare($sql);
$stmt -> execute(array($title, $entry));
$stmt ->closeCursor();
I have attached screenshots of my database in mysql. What could be the problem? Remeber I am an "ABSOLUTE BEGINNER" :p .
Thank you