How to Use MySQLi Prepared Statements

How to use mysqli prepared statements?

From the mysqli::prepare docs:

The parameter markers must be bound to application variables using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows.

bind_param docs.

i.e.:

$name = 'one';
$age  = 1;

$stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)");

// bind parameters. I'm guessing 'string' & 'integer', but read documentation.
$stmt->bind_param('si', $name, $age);

// *now* we can execute
$stmt->execute();

Best way to use MySQLi Prepared Statements in PHP

A procedural way... humm, been a long time since I did something procedural (strictly non OOP). I don't know why you want to limit yourself but I'll still leave PDO since it's best practice (from my POV)

Should I prepare one statement (or many) in each file I want to query the database?

I think you should prepare as many queries as you need and encapsulate your functionality so you don't have to copy paste

in your includes.php file

define a function that may be used in many locations

/**
 * Get User by Id
 * @returns user object or null
 */
function getUserById(PDO $connection, $id) {
  $q = 'SELECT * FROM `users` WHERE `id`=?';
  $stmt = $connection->prepare($q);
  $stmt->execute(array($id));
  return $stmt->fetchObject();
}

in your db/connect.php file

setup your PDO connection (this is my personal recomendation, I'm biased)

// do not copy paste, check the manual to see for yourself how it is done
$dsn = 'mysql:host='.$host.';dbname='.$data;
$connection = new PDO($dsn,$user,$password);

usage

whenever you need to get a user by his id just pass your database connection (PDO object) and the $id to the function

// functionality has been encapsulated and may be reused
$user = getUserById($connection,$id);

MySQLi prepared statements with IN operator

I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();

foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int); 

$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}

$result->free();
$data_res->close();
?>

php mysqli prepared statements select

I told you to limit your select function to a simple primary key lookup. And now you opened a can of worms. As a result you are getting entangled implementation code and unreadable application code.

$table, $args, $sort, $order, $clause

What all these variables are for? How you're going to call this function - a list of gibberish SQL stubs in a random order instead of plain and simple SQL string? And how to designate a list of columns to select? How to use JOINS? SQL functions? Aliases? Why can't you just write a single SQL statement right away? You already have a function for selects, though without this barbaric error reporting code you added to it:

function prepared_query($mysqli, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql)) { 
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

Just stick to it and it will serve you all right.

$sql = "SELECT * FROM `teste_table` WHERE id = ? AND username = ?";
$stmt = prepared_query($mysqli, $sql, [$id, $name]);
$row = $stmt->get_result()->fetch_assoc();

The only specific select function could be, again, a simple primary key lookup:

function crud_find($conn, $table, $id)
{
    $table = escape_mysql_identifier($table);
    $sql = "SELECT * FROM $table WHERE id=?";
    $stmt = prepared_query($conn, $sql, [$id], "i");
    return $stmt->get_result()->fetch_assoc();
}

And for the everything else just use a generic function with native SQL.

How to use MySQLi Prepared Statements with Stored Procedures

You might find the following answer of use:

MySql: Will using Prepared statements to call a stored procedure be any faster with .NET/Connector?

In addition:

GRANT execute permissions only so your application level user(s) can only CALL stored procedures. This way, your application user(s) can only interact with the database through your stored procedure API, they can not directly:

select, insert, delete, update, truncate, drop, describe, show etc. 

Doesn't get much safer than that. The only exception to this is if you've used dynamic sql in your stored procedures which I would avoid at all costs - or at least be aware of the dangers if you do so.

When building a database e.g. foo_db, I usually create two users. The first foo_dbo (database owner) is the user that owns the database and is granted full permissions (ALL) so they can create schema objects and manipulate data as they want. The second user foo_usr (application user) is only granted execute permisisons and is used from within my application code to access the database through the stored procedure API I have created.

grant all on foo_db.* to foo_dbo@localhost identified by 'pass';

grant execute on foo_db.* to foo_usr@localhost identified by 'pass';

Lastly you can improve your code example above by using mysql_real_escape_string:

• http://php.net/manual/en/function.mysql-real-escape-string.php

   $sqlCmd = sprintf("call usp_inserturl('%s','%s','%s')", 
    mysql_real_escape_string($longurl),  
    mysql_real_escape_string($shorturl), 
    mysql_real_escape_string($source));
  
   $result = mysql_query($sqlCmd);
  

How do you use IN clauses with mysqli prepared statements

Look at the answer to a similar question that has been asked here before (second code sample):

I have an array of integers, how do I use each one in a mysql query (in php)?

It boils down to:

• create the SQL string with the right amount of question marks
• use call_user_func_array() to bind your array to the query string

How to use PHP constants in Mysqli prepared statements

Since you're using a constant value, you're not exposing yourself to potential SQL injection attacks by concatenating the value into your query. So, I think what you have is fine. Your other option would be to assign the constant value to a variable and bind it, like this:

$query = "SELECT city FROM masters WHERE email = ? AND estado != ?";
$inactiveState = STATE_INACTIVE;
$stmt = $this->conn->prepare($query);
$stmt->bind_param("ss", $email, $inactiveState);

It's worth pointing out as well here that this is mysqli, not PDO. If you were using PDO you could do this:

$query = "SELECT city FROM masters WHERE email = ? AND estado != ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $email, PDO::PARAM_STR);
$stmt->bindValue(2, STATE_INACTIVE, PDO::PARAM_STR);

mysqli, prepared statements, and INSERT-SELECTs

First you create the statement very much like a normal statement you have made

$stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text)
SELECT c.id, ?
FROM categories AS c WHERE c.id = ?;");

Get the statement bound to the parameter 's' stands for string data and i for integer

$stmt->bind_param('si', $joke_text,$category_id);   // bind to the parameters

/* execute prepared statement */

$stmt->execute();

---

Related Topics