5

I am trying to write prepared statement for user input. parameter numbers are variable depends on user input. Oam trying this code

PHP code:

$string          = "my name";
$search_exploded = explode( " ", $string );
$num             = count( $search_exploded );
$cart            = array();
for ( $i = 1; $i <= $num; $i ++ ) {
    $cart[] = 's';
}
$str          = implode( '', $cart );
$inputArray[] = &$str;
$j            = count( $search_exploded );
for ( $i = 0; $i < $j; $i ++ ) {
    $inputArray[] = &$search_exploded[ $i ];
}
print_r( $inputArray );
foreach ( $search_exploded as $search_each ) {
    $x ++;
    if ( $x == 1 ) {
        $construct .= "name LIKE %?%";
    } else {
        $construct .= " or name LIKE %?%";
    }
}
$query = "SELECT * FROM info WHERE $construct";
$stmt  = mysqli_prepare( $conn, $query );
call_user_func_array( array( $stmt, 'bind_param' ), $inputArray );
if ( mysqli_stmt_execute( $stmt ) ) {

    $result = mysqli_stmt_get_result( $stmt );
    if ( mysqli_num_rows( $result ) > 0 ) {
        echo $foundnum = mysqli_num_rows( $result );
        while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {

            echo $id = $row['id'];
            echo $name = $row['name'];
        }
    }
}

When I print_r($inputArray), the output is:

Array ( [0] => ss [1] => my [2] => name )

There is no error showing in error log.

What is wrong?

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Vinay
  • 2,272
  • 4
  • 19
  • 34

2 Answers2

11

The % wrapping goes around the parameters, not the placeholders.

My snippet will be using object-oriented mysqli syntax instead of the procedural syntax that your code demonstrates.

First you need to set up the necessary ingredients:

  1. the WHERE clause expressions -- to be separated by ORs
  2. the data types of your values -- your values are strings, so use "s"
  3. the parameters to be bound to the prepared statement

I am going to combine #2 and #3 into one variable for simpler "unpacking" with the splat operator (...). The data type string must be the first element, then one or more elements will represent the bound values.

As a logical inclusion, if you have no conditions in your WHERE clause, there is no benefit to using a prepared statement; just directly query the table.

Code: (PHPize.online Demo)

$string = "Bill N_d Dave";

$conditions = [];
$parameters = [''];
foreach (array_unique(explode(' ', $string)) as $value) {
    $conditions[] = "name LIKE ?";
    $parameters[0] .= 's';
    // $value = addcslashes($value, '%_'); // if you want to make wildcards from input string literal. https://stackoverflow.com/questions/18527659/how-can-i-with-mysqli-make-a-query-with-like-and-get-all-results#comment132930420_36593020
    $parameters[] = "%{$value}%";
}
// $parameters now holds ['sss', '%Bill%', '%N_d%', '%Dave%']

$query = "SELECT * FROM info";
if ($conditions) {
    $stmt = $mysqli->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
    $stmt->bind_param(...$parameters);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($query);
}
foreach ($result as $row) {
    echo "<div>{$row['name']}</div>\n"; 
}

For anyone looking for similar dynamic querying techniques:

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
-1

Write a generic query handler and pass it your query, the array of parameters, and the list of parameter types. Get back an array of results or messages. Here's my own personal version for mysqli (I mostly use PDO, but have a similar function set up for that as well). Do the same for inserts, updates, and deletes. Then simply maintain your one library and use it for everything you do :) Note that if you start with this, you'll probably want to do a better job of dealing with connection errors, etc.

<?php

// this is normally in an include() file
function getDBConnection(){

    // your DB credentials

    $hostname="127.0.0.1";
    $username="ausername";
    $password="supersecret";
    $database="some_db_name";

    $con = new mysqli($hostname, $username,$password, $database);
    if($con->connect_error) {
      return false;
    }
    return $con;
}

// generic select function.
// takes a query string, an array of parameters, and a string of
// parameter types
// returns an array - 
//   if $retVal[0] is true, query was successful and returned data
//   and $revVal[1...N] contain the results as an associative array
//   if $retVal[0] is false, then $retVal[1] either contains the 
//   message "no records returned" OR it contains a mysql error message

function selectFromDB($query,$params,$paramtypes){

    // intitial return;
    $retVal[0]=false;

    // establish connection
    $con = getDBConnection();
    if(!$con){
        die("db connection error");
        exit;
    }

    // sets up a prepared statement
    $stmnt=$con->prepare($query);
    $stmnt->bind_param($paramtypes, ...$params);
    $stmnt->execute();

    // get our results
    $result=$stmnt->get_result()->fetch_all(MYSQLI_ASSOC);
    if(!$result){
    $retVal[1]="No records returned";
    }else{
        $retVal[0]=true;
        for($i=0;$i<count($result);$i++){
            $retVal[]=$result[$i];
        }
    }

    // close the connection
    $con->close();

    return $retVal;

}

$myusername=$_POST['username'];
$mypassword=$_POST['password'];

// our query, using ? as positional placeholders for our parameters
$q="SELECT useridnum,username FROM users WHERE username=? and password=?";

// our parameters as an array - 
$p=array($myusername,$mypassword);

// what data types are our params? both strings in this case
$ps="ss";

// run query and get results
$result=selectFromDB($q,$p,$ps);

// no matching record OR a query error
if(!$result[0]){
    if($result[1]=="no records returned"){
        // no records
        // do stuff
    }else{
        // query error
        die($result[1]);
        exit;
    }   
}else{  // we  have matches!
    for($i=1;$i<count($result);$i++){
        foreach($result[$i] as $key->$val){
            print("key:".$key." -> value:".$val);
        }
    }
}

?>
ivanivan
  • 2,155
  • 2
  • 10
  • 11