mysqli

[MichelDS]

Ok I am trying to use mysqli instead of the usual mysql.  Mysql would be outdated.

With mysqli, sgl-injection is impossible if you use the "?" in those codes.

 

I would normally use a function but  I've made a simple script to find the error.

 

I use $parameters and $sql because these are the data I need to give as parameters to the function, so I used it here too

but without the function actually.

 

ini_set('display_errors',1); // 1 == aan , 0 == uit
error_reporting(E_ALL | E_STRICT);
# sql debug
define('DEBUG_MODE',true);  // true == aan, false == uit


$userid = 11;
$lang = 1;
$newLink = "testing123";

$db_host = "localhost";
$db_gebruiker = "root";
$db_wachtwoord = '';
$db_naam = "projecteasywebsite";

$sql= "INSERT tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle)  VALUES(?, ?, ?, ?, ?, ?)"; 
$parameters = '"iiisis", $userid, 1, 0, $lang, 1, $newLink';	

echo $parameters;

$mysqli = new mysqli($db_host, $db_gebruiker, $db_wachtwoord, $db_naam);
$stmt = $mysqli->prepare($sql);

$stmt->bind_param($parameters);  	

$stmt->execute();

echo "<br><br>". mysqli_connect_errno();
echo "<br><br>". mysqli_report(MYSQLI_REPORT_ERROR);

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

 

I got Wrong parameter count for mysqli_stmt::bind_param() 

 

So naturally a problem when we execute : Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: (HY000/2031): No data supplied for parameters in prepared statement    ($stmt->execute()

 

Is someone using mysqli too ?

 

[scootstah]

You are passing bind_param a single parameter. You can't just pass in a comma-separated string and expect to have multiple parameters, it doesn't work that way. It should be like this:

$stmt->bind_param('iiisis', $user_id, 1, 0, $lang, 1, $newLink); 

 

You can however use call_user_func_array to call the bind_param method, but IIRC it is a pretty sloppy-looking alternative.

[MichelDS]

But the $stmt->bind_param(...) is a part of the function so I can't use this outside the function.

 

Is there any code that  makes it as one string so it can be passed along to the function ?

[scootstah]

But the $stmt->bind_param(...) is a part of the function so I can't use this outside the function.

 

Huh?

[MichelDS]

In this example there's no function but I will need to use it with a  function and put :  "iiisis", $user_id, 1, 0, $lang, 1, $newLink    throught the function into  $stmt->bind_param(...)  that's in the function.

The function is to make connection with the database and gives back data from a table  or does an update/delete/insert data.

 

The function is  :

 

function ShowSaveDelIntoDB($sql, $action, $parameters){
global $db_host; 
global $db_user; 
global $db_pass; 
global $db_name;
$ResultShow ='';	

$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

if (mysqli_connect_errno()) {
	return("No connection with database : %s\n" . mysqli_connect_error());
	exit();
	}
if(isset($action) && $action == "Show" ){ 
	$ResultShow = $mysqli->query($sql); 
	if(!$ResultShow){
		return "A problem has occured : " .mysqli_report(MYSQLI_REPORT_ERROR)."";
		}
	else {
		return $ResultShow;
		}		
	}				
else{
	if ($stmt = $mysqli->prepare($sql))
		{
		$stmt->bind_param($parameters);     
		$stmt->execute();
		//return $mysqli->insert_id;				
		$stmt->close();				
		}				
	}
 $mysqli->close();
}

 

I hope that now it is more uderstandable.

 

 

I've looked at call_user_func_array()  at http://be.php.net/call_user_func_array and don't understand much of it.

 

$sql = "INSERT tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle)  VALUES(?, ?, ?, ?, ?, ?)";

$parameters = '"iiisis", $userid, 1, 0, $lang, 1, $newLink';

 

call_user_func_array(array(ShowSaveDelIntoDB, '__construct'), array($sql,"",$parameters)); gives me plenty errors...

 

If it don't work, I'll work with mysql again because it works very well, -because I don't have the problem with $parameters !

But the codes to secure the input into the database is so ...  :'(  That's mysql ! 

 

That's why I try mysqli.  The problem is $parameters.

 

 

 

[scootstah]

global $db_host; 
global $db_user; 
global $db_pass; 
global $db_name;

 

First of all, don't do that. Bad bad bad. You should be connecting to the database in a single, static location - then just include that file when you need a connection. You will have to pass the mysqli object to your functions to work with it, or use some form of dependency injection, or at the very least a Singleton wrapper. All of these are better than what you're currently doing.

 

Secondly your function has far too many responsibilities but beyond that, it doesn't make a whole lot of sense. You are going to be writing the query out to use the function anyway so you can't even argue that it is a query-builder function. I don't really see a purpose to it.

 

With that out of the way, you are using call_user_func_array incorrectly. I haven't tested this but I think the following will work for dynamic parameter-binding. Note that this is a little easier in PDO since you don't need to supply types.

// loop through the parameters to figure out the types
$types = '';

foreach($parameters as $parameter)
{
if (is_int($parameter)) {
	$types .= 'i';
} else if (is_float($parameter)) {
	$types .= 'd';
} else {
	$types .= 's';
}
}

// add the types to the beginning of the parameters array
array_unshift($parameters, $types);

call_user_func_array(array($stmt, 'bind_param'), $parameters);

[MichelDS]

I understand what you're saying that the function has far too many responsibilities.

My goal was, a function that execute any sql query. Simple.  the function opens the DB, execute the query  and close the DB, than returns the result (if there's any) .

That's it. No fancy stuff here. Just making it easy.

 

I've researched  "dependency injection" and "Singleton wrapper". Everytime I found the sql query included in the function. So for every other (different) query I would need another function. That's crazy.

And it does somewhat the same as my function, only it's object oriented ( to difficult for me at this time, I'm new into PHP and did ASP before)  and you got multiple functions and much more code. So I don't get that.  Is it more safe ? I don't think so.

 

If my function would have an problem someday, nothing works, no update/insert/delete...  I understand this.  I should better make a function for each action. And I'll do this I think.  Because I'm new into PHP the function only works with a global statement. Don't ask me why.  I don't know why. That's why I used global.

 

But I tried your code but know really how to use the code within the function.

 

For example    call_user_func_array(array($stmt, 'bind_param'), $parameters);   should come where ? 

if ($stmt = $mysqli->prepare($sql))              
{
     $stmt->bind_param($parameters);     // replace this ?   
     $stmt->execute();			
     $stmt->close();		
}

[scootstah]

Here's the thing though: every time you run that function you are opening/closing a database connection, which is just unnecessary overhead and a waste of resources. Establish the database connection in a single, static location and then at the very least pass the connection resource in global's. Although it's still bad practice, it is better than opening/closing the connection each time a query is run.

 

$stmt->bind_param($parameters);     // replace this ?

 

Yes.

[MichelDS]

I didn't succeeded to make this work. Thus with regret I have to do it in a very stupid and extra long way

$mysqli = opendb($db_host, $db_user, $db_pass, $db_name);
if(!$mysqli){
	echo "An error has occured, no data available";
	}
else {
   $sql= "INSERT tbl_link(userid, linkcat, linksubid, linklang, linkactive, linktitle) VALUES(?, ?, ?, ?, ?, ?)"; 
if ($stmt = $mysqli->prepare($sql)) {

    $stmt->bind_param("iiiiis", $userid , $one, $null, $lang , $one, $newLink);
    $stmt->execute(); 
    $newlinkid = mysqli_insert_id($mysqli);  //$mysqli_stmt->insert_id();   // I need the inserted  last row ID
    $stmt->close();	

}
$mysqli->close();

 

The only function is opendb(...) with the variables init, this way I don't need  to use the global statement.  But how long like a  prehistoric age ! 

 

So I don't have a choice anymore, my knowledge is far too little right now. Thanks  for all the help you gave.

[scootstah]

You are still creating the connection on each function call. And this isn't going to solve not using global's, because those variables don't exist in the function scope.

 

What you need to do is this:

1. Create a file called "connect.php"

2. In the file, put this code:

<?php
$mysqli = @new mysqli('localhost', 'root', 'root', 'dbname');

if ($mysqli->connect_error) {
die('Could not establish database connection');
}

 

3. From now on whenever you need a script to have database access, simply run include 'connect.php'; at the top of the script. The script will then have access to the $mysqli object and you can access the database.

4. For this to work with your function you will either need to make the $mysqli object global or pass it into the function (the recommended way). Something like:

function runSomeQuery(mysqli $mysqli)
{
}

 

5. Now when you call the function simply pass the object in:

<?php

include 'connect.php';

// ...

runSomeQuery($mysqli);

 

You now have full access to the $mysqli object inside your function.

 

Also, you don't need to (and shouldn't) explicitly close the database connection; PHP will do it for you automatically.

[MichelDS]

You don't give up isn't ?

You talk about the function that I could not letwork !

So I've posted the code. Just for inserting data. That's my test but It gives me a some errors.  (see below)

 

I am in the beginning of a huge project , so it's important that I decide from the beginning which code to use and how from getting/inserting/...  data from/into the database. 

Only this thing with mysqli is not so simple if you want to make a function with it apparently !

 

 

<?php
// variables that normally comes from somewhere else
$userid = "11";
$lang ="1";
$newLink = "testing12345";
$one = 1;
$null = 0;

include_once('includes/dsn_start.php');     // Opening the datase just as you said. 


function ShowSaveDelUpdateDB($mysqli, $sql, $parameters){
if ($stmt = $mysqli->prepare($sql))
	{	
	//$stmt->bind_param($parameters);    

	// loop through the parameters to figure out the types
	$types = '';
	foreach($parameters as $parameter)
	{
		if (is_int($parameter)) {
			$types .= 'i';
		} else if (is_float($parameter)) {
			$types .= 'd';
		} else {
			$types .= 's';
		}
	}
	// add the types to the beginning of the parameters array
	array_unshift($parameters, $types);
	call_user_func_array(array($stmt, 'bind_param'), $parameters);

	$stmt->execute();
	//return $mysqli->insert_id;				
	$stmt->close();				
	}				

} //end function

// Here I'm asking the function to insert

$sql = "INSERT tbl_link(userid, linkcat, linksubid, linklang, linkactive, linktitle) VALUES(?, ?, ?, ?, ?, ?)"; 
$parameters = "$userid , $one, $null, $lang , $one, $newLink";
ShowSaveDelUpdateDB($mysqli, $sql, $parameters)	;

?>

 

Warning: Invalid argument supplied for foreach()    -> code : foreach($parameters as $parameter)

Warning: array_unshift() expects parameter 1 to be array, string given    -> code : array_unshift($parameters, $types);

Warning: call_user_func_array() expects parameter 2 to be array, string given    -> code : call_user_func_array(array($stmt, 'bind_param'), $parameters);

 

[scootstah]

Make $parameters an array not a string:

$parameters = array($userid, $one, $null, $lang, $one, $newLink);

[MichelDS]

It continues...

 

I changed to become an array : $parameters = array("$userid , $one, $null, $lang , $one, $newLink");

 

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given -> code : call_user_func_array(array($stmt, 'bind_param'), $parameters);

 

If it should work at the end I'll post the final solution here too !

[scootstah]

Yeah, that was one of the quirky things about doing it this way. mysqli_stmt::bind_param only accepts parameters passed by reference for whatever reason. Add this code right before

// add the types to the beginning of the parameters array
	array_unshift($parameters, $types);

 

$parameters_ref = array();

foreach(array_keys($parameters) as $key)
{
$parameters_ref[$key] = &$parameters[$key];	
}

 

Also you are setting the $parameters array incorrectly. Look carefully at the syntax of mine vs yours.

[mikosiko]

there are several examples here --> http://php.net/manual/en/mysqli-stmt.bind-param.php that show different ways to implement what you are trying to do... just read the examples and adapt the one that you like the most... there are even explanation and solution for your last error.

[MichelDS]

Ok, this is what I have and it works ! You may congratulate yourself ! 

 

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors',1); // 1 == on , 0 == off
# sql debug
define('DEBUG_MODE',true);  // true == on, false == off


include_once('../inc/dsn_start.php');   // Open DB


function ShowSaveDelUpdateDB($mysqli, $sql, $parameters){

//INSERT, UPDATE, DELETE
if ($stmt = $mysqli->prepare($sql))
{	
$parameters_ref = array();

foreach(array_keys($parameters) as $key)
{
	$parameters_ref[$key] = &$parameters[$key];	
}		

// loop through the parameters to figure out the types
$types = '';
foreach($parameters as $parameter)
{
	if (is_int($parameter)) {
		$types .= 'i';
	} else if (is_float($parameter)) {
		$types .= 'd';
	} else {
		$types .= 's';
	}
}
// add the types to the beginning of the parameters array
array_unshift($parameters, $types);
call_user_func_array(array($stmt, 'bind_param'), $parameters);

$stmt->execute();
return $mysqli->insert_id;			
$stmt->close();				
}		

} //end function


# INSERT
$sql = "INSERT tbl_link(userid, linkcat, linksubid, linklang, linkactive, linktitle) VALUES(?, ?, ?, ?, ?, ?)"; 
$parameters = array($userid, $one, $null, $lang, $one, $newLink);
$LastRowId = ShowSaveDelUpdateDB($mysqli, $sql, $parameters);

# UPDATE 
//$sql = "UPDATE  tbl_link SET linktitle = ? WHERE linkid = ?";
//$parameters = array($linktitle, $linkid);
//ShowSaveDelUpdateDB($mysqli, $sql, $parameters);

# DELETE
//$sql = "DELETE FROM tbl_link WHERE linkid = ?"; 
//$parameters = array($linkid);	
//ShowSaveDelUpdateDB($mysqli, $sql, $parameters);

 

 

 

 

Okay now, let's makes it compleet and do the  INSERT part.

I got this ready (only INSERT in tin this one so it'll be not confusing, I'll put it together with the rest of the original function  later.

I got message "return "No results to display !";" + the error when writing out the result of the query : Notice: Trying to get property of non-object      -> code  :  if ($result->num_rows > 0)

 

 

function ShowSaveDelUpdateDB($mysqli, $sql, $parameters){

// to detect if there's "SELECT ..." in the query :
$phrase = explode (" ", $sql);
if(in_array('SELECT',$phrase) OR in_array('select',$phrase))
{


//same code as usual :
	if ($stmt = $mysqli->prepare($sql))
		{	
		$parameters_ref = array();

		foreach(array_keys($parameters) as $key)
			{
			$parameters_ref[$key] = &$parameters[$key];	
			}		

		// loop through the parameters to figure out the types
		$types = '';
		foreach($parameters as $parameter)
		{
			if (is_int($parameter)) {
				$types .= 'i';
			} else if (is_float($parameter)) {
				$types .= 'd';
			} else {
				$types .= 's';
			}
		} // end for each

		// add the types to the beginning of the parameters array
		array_unshift($parameters, $types);
		call_user_func_array(array($stmt, 'bind_param'), $parameters);				
		$stmt->execute();


// new part  :
		$meta = $stmt->result_metadata();
		while ($field = $meta->fetch_field()) 
		{
		  $param[] = &$row[$field->name];
		}
		call_user_func_array(array($stmt, 'bind_result'), $param);
		while ($stmt->fetch()) 
		{
		  foreach($row as $key => $val) 
		  {
			$x[$key] = $val;
		  }
		  $results[] = $x;
		} 
		$stmt->close();				
		return $results;				


	} // end if
	else 
	{
	return "No results to display !";
	}	
} // end IF SELECT...

} // close function


// Here we're asking to the function to give us a result
# SELECT
$sql = "SELECT * FROM tbl_link WHERE userid = ?";
$parameters = array($userid);
$result = ShowSaveDelUpdateDB($mysqli, $sql, $parameters);



// Code to SHOW the records from the database 
if (!$result)
{
echo "No valide data !";
}
else
{
// display records if there are records to display
 if ($result->num_rows > 0)
 {
	 echo "<table border='1' cellpadding='10'>";
	 echo "<tr><th>LinkID</th><th>Linksuborder</th><th>Linktitle</th></tr>";
                                        
	while ($row = $result->fetch_object())
	 {
		// set up a row for each record
		echo "<tr>";
		echo "<td>" . $row->linkid . "</td>";
		echo "<td>" . $row->linksuborder . "</td>";
		echo "<td>" . $row->linktitle . "</td>";
		echo "</tr>";
	}
	echo "</table>";
}
	// if there are no records in the database, display an alert message
 else
{
echo "No results to display !";
 }
}

[MichelDS]

you may congratulate yourself scootstah I mean !

[MichelDS]

?

[MichelDS]

okay, I solved it myself 

 

So I post the final code here for the use of Mysqli  and the use of a function to retrieve the data or to insert/update or delete the data.

 

Thanks to scootstah ! He had solved the most difficult part.

 

This script uses "?" in the sql query  instead of the fieldnames from the table in the database. This should be more secure concerning SQL injection.

Also the script don't need you to say if the field is an integer of a string or something else.

Normally you will find something as :

$stmt->bind_param("si", $firstname, $id);

Here there are two fieldnames, the first is a string (firstname) and is written as "s", the second is an integer (id) and is written as "i".

This is not necessary anymore, only the fieldnames are required ! The function does the rest.

 

 

include_once('inc/dsn_start.php');  // Open DB

<?php
$db_host = "localhost";
$db_gebruiker = "your_username";
$db_wachtwoord = 'your_password';
$db_naam = "your_database_name";

$mysqli  = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

if ($mysqli->connect_error) {
echo ('Could not establish database connection'); 
       // or the "die" code 
       // or something else... 
}
?>

 

 

2 functions : SelectFromDB() for the SELECT sql and  InsertDelUpdateDB() for the  INSERT, UPDATE and DELETE sql query.

<?php
function SelectFromDB($mysqli, $sql, $parameters){
//just check if it is the right SQL query "SELECT ..."
$phrase = explode (" ", $sql);
if(in_array('SELECT',$phrase) OR in_array('select',$phrase))
{
	if ($stmt = $mysqli->prepare($sql))
		{	
		$parameters_ref = array();

		foreach(array_keys($parameters) as $key)
			{
			$parameters_ref[$key] = &$parameters[$key];	
			}		

		// loop through the parameters to figure out the types
		$types = '';
		foreach($parameters as $parameter)
		{
			if (is_int($parameter)) {
				$types .= 'i';
			} else if (is_float($parameter)) {
				$types .= 'd';
			} else {
				$types .= 's';
			}
		} // end for each

		// add the types to the beginning of the parameters array
		array_unshift($parameters, $types);
		call_user_func_array(array($stmt, 'bind_param'), $parameters);				
		$stmt->execute();
			$result = $stmt->get_result();
		$stmt->close();				
		return  $result;					

	} // end if
	else 
	{
	return "No results to display !";
	}	
}
} // end function




//INSERT, UPDATE, DELETE
function InsertDelUpdateDB($mysqli, $sql, $parameters){

if ($stmt = $mysqli->prepare($sql))
{	
$parameters_ref = array();

foreach(array_keys($parameters) as $key)
{
	$parameters_ref[$key] = &$parameters[$key];	
}		

// loop through the parameters to figure out the types
$types = '';
foreach($parameters as $parameter)
{
	if (is_int($parameter)) {
		$types .= 'i';
	} else if (is_float($parameter)) {
		$types .= 'd';
	} else {
		$types .= 's';
	}
}
// add the types to the beginning of the parameters array
array_unshift($parameters, $types);
call_user_func_array(array($stmt, 'bind_param'), $parameters);

$stmt->execute();
return $mysqli->insert_id;			
$stmt->close();				
}		

} //end function

?>

 

Here the codes with the SQL statement, you choose what you need

<?php
# SELECT
$sql = "SELECT * FROM tbl_link WHERE userid = ?";
$parameters = array($userid);
$result = SelectFromDB($mysqli, $sql, $parameters);

# INSERT
$sql = "INSERT tbl_link(userid, linkcat, linksubid, linklang, linkactive, linktitle) VALUES(?, ?, ?, ?, ?, ?)"; 
$parameters = array($userid, $one, $null, $lang, $one, $newLink);
$LastRowId = InsertDelUpdateDB($mysqli, $sql, $parameters);
        // you can retrieve the rowid where the data have been inserted 
        // echo $LastRowId;

# UPDATE 
$linktitle = "new test 3,2,1,";
$sql = "UPDATE  tbl_link SET linktitle = ? WHERE linkid = ?";
$parameters = array($linktitle, $linkid);
InsertDelUpdateDB($mysqli, $sql, $parameters);

# DELETE
$sql = "DELETE FROM tbl_link WHERE linkid = ?"; 
InsertDelUpdateDB($mysqli, $sql, $parameters);
?>

 

If you used a "SELECT" sql query, now we will display the data

<?php
if (!$result)
{
echo "No valid data !";
}
else
{
// display records if there are records to display
 if ($result->num_rows > 0)
 {
	while ($row = $result->fetch_object())
	 {
		// set up a row for each record

		echo  $row->linkid." - ".$row->linksuborder." - ".$row->linktitle."<br />";
	}
}
	// if there are no records in the database, display an alert message
 else
{
echo "No results to display !";
 }
}

?>

 

Have fun with it...