CODEWITHSUNDEEP
phpmysqliprepared-statement
Dumbgenius
Dumbgenius

Reputation: 58

MySQLi dynamically created prepared statement fails: "No data supplied for parameters in prepared statement"

I have a piece of code that reads a crash log and other metadata from a MySQL database before sending it back to the requester as JSON (this code is called by an AJAX function on another page). Optionally, filters can be specified with GET, which alter the prepared statement to filter for only the specified results - for example, to only show results from one app version. Here is the code in question:

$conn = new mysqli($sql_servername, $sql_username, $sql_password, "Crashes");
if ($conn->connect_error) {
    //failed
    die();
}

$sql="
SELECT ID,phone,appver,osver,user_agent,username,message,app_name,date_received FROM Crashes WHERE ";
$params="";
$params2="";
if (isset($_GET["filter_phone"])) {
    $sql .= "phone = ? AND "; //i.e "WHERE phone = ? AND (...osver = ? AND appver = ? etc)"
    $params.="s";
    $params2.=', $_GET["filter_phone"]';
}
if (isset($_GET["filter_appver"])) {
    $sql .= "appver = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_appver"]';
}
if (isset($_GET["filter_osver"])) {
    $sql .= "osver = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_osver"]';
}
if (isset($_GET["filter_user_agent"])) {
    $sql .= "user_agent = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_user_agent"]';
}
if (isset($_GET["filter_username"])) {
    $sql .= "username = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_username"]';
}
if (isset($_GET["filter_message"])) {
    $sql .= "message = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_message"]';
}
if (isset($_GET["filter_app_name"])) {
    $sql .= "app_name = ? AND ";
    $params.="s";
    $params2.=', $_GET["filter_app_name"]';
}
$sql.="1";

//echo "\$params: ".$params."<br>";
//echo "\$params2: ".$params2."<br>";
//echo "<br>\$stmt->bind_param(\$params$params2);<br>";
//echo var_dump($_GET);

$stmt = $conn->prepare($sql);
exec("\$stmt->bind_param(\$params$params2);");

if ($stmt->execute()) {
    //echo "<br>Success!";
} else {
    //echo "<br>Failure: ".$stmt->error;
}
$result = $stmt->get_result();
$out = array();
while ($row = $result->fetch_assoc()) {
    array_push($out, $row);
}
echo json_encode($out);

This code works perfectly when no filters are specified - however, when any filter is specified, the statement fails with the error No data supplied for parameters in prepared statement. I have verified that the code in the exec() should be (for example, if the user_agent filter is set) $stmt->bind_param($params, $_GET["filter_user_agent"]);

Additionally, the SQL query works perfectly when I use the query but just manually replace the ?s with the filters, so it does not appear to be a problem with the initial query, but rather the prepared statement. Help would be much appreciated.

NB: The commented out echos are for debug purposes, and are usually disabled, but I have left them in to show how I got the information that I currently have.

EDIT: Answered my own question below - the error was that I was using exec() - a function which executes external shell commands, whereas what I wanted was eval(), which evaluates a string input and the executes it as a PHP script.

Upvotes: 0

Views: 118

Answers (2)

Dumbgenius
Dumbgenius

Reputation: 58

Turns out I was simply mistaken as to the function of exec(). (Too much Python?) exec() runs an external shell command, whereas what I was looking for was eval() which evaluates and runs any string inputted as PHP code.

Upvotes: 1

nickdnk
nickdnk

Reputation: 4300

Looks like you never actually use the values $params and $params2. You concatenate them, but where is your $stmt->bind_param()? It's commented out?

I don't see a $conn->prepare("") either. Did you omit some code?

$conn is defined as a mysqli object and then again never used. Something's not right here.

Edit:

Try exec("\$stmt->bind_param(\$params\$params2);"); I assume you somehow execute the code and escape the variables - following that logic you should escape both params and params2, perhaps?

Upvotes: 0

Related Questions