Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Prepared statements and instantiation

    I have a working database accessed through php and mysqli. To increase security I am in the process of replacing legacy queries with prepared statements. I am following a number of tutorials and forum entries and I get the proper results following the examples rote, but when I try to adapt the code to my database the stream of errors don't seem to end -- so obviously, I am doing something very wrong and I think it is improper instantiation.

    I have highlighted the point of the first error in red -- I have tried substituting $db for $mysqli and I get an undefined variable error. ...also tried instantiating $mysqli and a double instantiation error occurs - I expect that it is illegal to connect to the same database twice.

    It is probably obvious that this is my first effort at prepared statements -- I would greatly appreciate an explanation of a fix in addition to the code. Thanks for your help.

    This is as far as I have gotten:

    Code:
    <?php
    require_once('../css/styles.PNS.php');  
    require_once('class.dbConnect.php');
    
    $db = new db(array('server', 'username', 'password', 'database'));
    
    if(!empty($_POST["nameSearch"])) {$searchField="Complex_Name";$recSearch = $_POST["nameSearch"];
    
    //$sql="SELECT * FROM Complex WHERE ". $searchField . " LIKE '%" . $recSearch . "%'";
    
    $sql="SELECT * FROM Complex WHERE ". $searchField . " LIKE '%?%'";
    $stmt = $mysqli_prepare($db,$sql);
    $stmt->bind_param('s',$recSearch);
    $stmt->execute();
    $stmt->store_result();
    The following is the required include file, class.dbConnect.php:

    Code:
    class db
    {
    
    	public $result = "";
    	public $db = array();
    	private $connection;
    	
    	public function db() {
    			$this->db['server']		= 'localhost';
    			$this->db['username'] 	= 'root';
    			$this->db['password'] 	= 'password';
    			$this->db['database'] 	= 'PNS_DB';
    			
    	$this->connection = mysqli_connect($this->db['server'], $this->db['username'], $this->db['password'], $this->db['database']);
    	}
    	
    	public function connection() {
    			$this->connection = mysqli_connect($this->db['server'], $this->db['username'], $this->db['password'], $this->db['database']);
    			$this->select_db(); 
    	}
    	
    	public function disconnect() {
    			mysqli_close($this->connection);
    			$this->connection = null;
    	}
    	
    	public function select_db() {
    			mysqli_select_db($this->connection, $this->db['database']); 
    			echo $this->connection;
    	}
    	
    		public function query($sql) {
    
    			$this->connection = mysqli_connect($this->db['server'], $this->db['username'], $this->db['password'], $this->db['database']);
    			$this->result = mysqli_query($this->connection, $sql);
    			$result=$this->result;
    			return $result;
    	}
    	
    		public function is_connected() {
    				return ($this->connection) ? true : false; 
    	}
    	
    }

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,592
    Thanks
    0
    Thanked 645 Times in 635 Posts
    replace:

    $stmt = $mysqli_prepare($db,$sql);

    with:

    $stmt = $db->prepare($sql);

    Then it will be using the same way of making the call as the other statements around it.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I gave that a try - the resulting error message is:

    Code:
    Fatal error: Call to undefined method db::prepare() in /srv/www/htdocs/PNS/Complex/tableList10.php on line 26

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    The point of having a database class/wrapper is that it contains all the lower-level database logic and your application (main code) only forms the queries and validates/supplies the data being put into the queries and calls the database class methods to actually perform the database operations.

    The error message is pretty self explanatory though, your db class doesn't have a ->prepare() method.

    You db class does have a ->query() method, but it is foo-bar. You should not be making a database connection every time you run a query. You should be making a database connection once when you create an instance of the db class.

    Get your existing db class organized and working with a normal (non-prepared) query, then add a prepared query method to it with the logic necessary to prepare, dynamically bind any input data, run the query, check for errors, dynamically bind any result,
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    CFMaBiSmAd -> I see your point on reconnecting to the database with each query, and I will definitely fix that.

    But what about $mysqli->prepare()? That is a built-in function that should be accessible without re-creating it in my db class. Again, I think that problem lies with instantiation - These prepared statements work fine outside my script - but do not work when I tried to integrate them, so I am doing something wrong... Any thoughts?

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    The $db variable is an instance of your class. It's not an instance of the msyqli class. When you call methods of your class, they must exist in your class.

    An alternative would be for your class to extend the mysqli class. In this case, if you called the ->prepare() method, it would be calling the base mysqli ->prepare() method.

    However, I suspect your intention is to reduce the amount of code you have in your main application code to run each prepared query by having a class method that performs all the repetitive steps needed when you prepare/bind inputs/execute/bind the result/and fetch the data?

    You kind of need to define what you are trying to accomplish with your db class before you go any further.

    As to your existing class definition, it's not using the call-time connection details and it has three different places where it can make a database connection. It needs definition and cleanup before you try to add anything else to it.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    CFMaBiSmAd -> I tested my db class. The connection occurs only one time and multiple queries do not result in multiple connects. So it appears to be working as intended. You previously mentioned defining my intentions; I want to replace my legacy (insecure) queries with prepared statements to increase data security over the net. As written, everything works as designed and no known bugs exist.

    So, can anyone explain what I am doing wrong with prepared statements? It appears to me that the db instantiation conflicts with mysqli instantiation. Both require a connection to the database which is throwing the fatal errors. How do I incorporate an instantiation of mysqli without breaking the code?

  • #8
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have discovered that multiple instantiations are not the problem. I have instantiated both $db and $mysqli and they work fine together.

    So I am working my way through prepared statements and have a partial solution, but also a snag. The following function is a means of using prepared statements without having to declare the bound parameters in advance - in essence, it provides the flexibility of choosing fetched fields dynamically.

    I have successfully pulled the fields names, but the values are not being fetched. The function works as expected until the while ($stmt->fetch()) conditional statement, which is not looping. This failure may be related to the previous line; the call_user_func_array statement (both statements highlighted in red). Although I have read the manual on the callback statement, I don't have a good understanding of its function or how to test it, and I think that is the place to start.

    For reference, this function replaces code at and below the $mySQL instantiation posted above, although I think this post can be evaluated on its own.

    Code:
    function read($recSearch)  
    	{  
    	$parameters = array();  
    	$results = array();
    	$mysqli = new mysqli('localhost','root','password','PNS_DB');
    	$sql="SELECT Complex_ID,Complex_Name FROM Complex WHERE ". $recSearch . " LIKE CONCAT('%', ?, '%')";
    	$stmt = $mysqli->prepare($sql);
    	$stmt->execute();
    	$meta = $stmt->result_metadata();  
    
    	while ( $field = $meta->fetch_field() ) {
    	$parameters[] = &$row[$field->name];
    	}
    
    	call_user_func_array(array($stmt, 'bind_result'), $parameters);
    	while ( $stmt->fetch() ) 	
                     {  
    		echo "while command";
    		$x = array();  
    		foreach( $row as $key => $val ) 
    		{  
    			$x[$key] = $val;
    			echo "values=",$key,$val;  
    		}  
    	$results[] = $x;  
    	}  
    	return $results;  
    } 
    $results=read($searchField);
    foreach ($results as $row) :  
    echo $row['Complex_ID'];  
    echo $row['Complex_Name'];
    endforeach;

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    The code you posted isn't working because your ->execute() method failed because you are not binding any input parameter for the ? placeholder in the query. If you were checking for errors from the ->execute() method you would be getting - No data supplied for parameters in prepared statement

    If you are going to write an advanced database function/class, you must have error checking logic in your code so that you don't attempt to use data that doesn't exist.

    The ->prepare() method can fail due to sql syntax errors, the bind_parm code can fail with an error, the ->execute() method can fail with an error, and the bind_result code can fail with an error. You must check at each step if your code worked before trying to use the result from that step.
    Last edited by CFMaBiSmAd; 05-27-2013 at 12:09 AM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #10
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks to CFMaBiSmAd for the suggestion on error checking. After adding that coding I discovered there was an error in my query that chunked up the debugging further down. Good call, I will remember that step in the future!

    Goal: To build an associative array in the format: field_name => value.

    I have made significant headway; I can display field names and values but now I have a head-scratcher... Array $parameters[] serves double duty: It first collects the field names in the first loop but they are overwritten in the second loop. I need to save both field names and values to build the associative array, but every attempt to do so breaks the second loop.

    Notes: $parameters[] initially contains the field names but those are lost during the 2nd loop when $parameters[] elements are filled with values. I tried assigning $fields to $columnNames[] but that broke the 2nd loop and I don't know why... Then I tried assigning $parameters[] to $columnNames[] and that also broke the 2nd loop. I also tried using different array names to separate field names from values, but every attempt breaks the 2nd loop. If that isn't enough, if I comment out the first loop, the 2nd loop breaks and I don't understand that dependency either.

    So here's my latest code. As always, please explain the logic along with any code offerings. Thanks!

    Code:
    function read($recSearch)  
    	{  
    	$parameters = array();
    	$columnName = array();  
    	$results = array();
    	$mysqli = new mysqli('localhost','root','password','PNS_DB');
    		if ($mysqli->connect_error) {die('Connect Error (' . $mysqli->connecto_errno . ') ' . $mysqli->connect_error);}
    
    	$sql="SELECT Complex_ID,Complex_Name FROM Complex WHERE Complex_Name LIKE CONCAT('%', ? , '%')";
    
    	$stmt = $mysqli->prepare($sql);
    		if (false === $stmt) {die('Query Prep Failed' . $mysqli->error);}
    	$stmt->bind_param('s',$recSearch);   //string
    	$result = $stmt->execute();
    		if (false===$stmt) {die('execute() failed: '.$stmt->error);}
    	$meta = $stmt->result_metadata();  
    	while ( $field = $meta->fetch_field() ) 
    	{
    	$parameters[] = $field->name;   Yields field names - required for second loop -- why?
    //	$columnName[] = $field->name;   This statement broke the 2nd while fetch loop -- why?
    	}
    //	$columnName = $parameters;      This statement broke the 2nd while fetch loop -- why?
    
    	$x = call_user_func_array(array(&$stmt, 'bind_result'), &$parameters);
    	if (false === $x) {die('bind_param()error :' . $mysqli->error);}
    
    	while ( $stmt->fetch() ) 
    	{  
    		$x = array();
    		foreach( $parameters $key=>$val) 
    		{
                          echo "key=",$key," "          // Yields array element number
    	              echo "val=",$val,"<br />";    // Yields the array element contents
    
    		}  
    	}  
    	return $results;  
    }   
    $results=read($recSearch);
    Last edited by geno11x11; 05-31-2013 at 06:40 AM.

  • #11
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    A more specific question: In the short snippet below, $columnNames[] contains field names in the top while loop prior to the bind statement, and then contains values after the bind statement. How do I place the field names in a separate array prior to the bind? Every effort to do so has broken the foreach loop.

    Code:
    while ( $field = $meta->fetch_field() ) 
    	{
    	$columnNames[] = $field->name;
    	}
    
    	$x = call_user_func_array(array(&$stmt, 'bind_result'), &$columnNames);
    	if (false === $x) {die('bind_param()error :' . $mysqli->error);}
    
    	while ( $stmt->fetch() ) 
    	{  
    	foreach( $columnNames as $key=>$val) 
    		{  
    			echo "val=",$val,"<br />";
    		}  
     
    	}
    Last edited by geno11x11; 06-01-2013 at 08:47 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •