Asked  7 Months ago    Answers:  5   Viewed   63 times

Let's pretend that I have two tables in an InnoDB database: categories and jokes; and that I'm using PHP/MySQLi to do the work. The tables look like so:

CATEGORIES
id (int, primary, auto_inc)  |  category_name (varchar[64])
============================================================
1                               knock, knock

JOKES
id (int, primary, auto_inc)  |  category_id (int)  | joke_text (varchar[255])
=============================================================================
empty

Thanks to a previous answer on here, I discovered that you could do the following in order to add a new joke comprised of: $joke_text, $category_id.

INSERT INTO jokes (category_id, joke_text)
SELECT c.id, '$joke_text'
FROM categories AS c WHERE c.id = $category_id;

This enables me, without the use of foreign keys, to be sure that the $category_id value refers to an existing category (please ignore the issue of foreign keys, as my question is aimed at helping me learn "complicated" prepared statements).

So that worked just fine. However, I am now trying to learn prepared statements and, after spending all day on it, I finally have the basics down. Unfortunately, I have ABSOLUTELY NO IDEA how to to execute the above SQL query with prepared statements, under mysqli, and I have not been able to find any info online regarding such an issue.

If anyone can help me out, I'd be very appreciative.

 Answers

14

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();
Saturday, May 29, 2021
 
Saxophlutist
answered 7 Months ago
11

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Wednesday, March 31, 2021
 
Keat
answered 9 Months ago
21

The characters in the string should not be separated by commas:

$stmt->bind_param("sss...", /* variables */);

You can see this format demonstrated in the examples on the manual page.

Wednesday, June 2, 2021
 
chugadie
answered 7 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 4 Months ago
17

Having such a function is a good idea per se. It indicates that you are a programmer in your heart, not just a tinkerer that writes PHP from ready made blocks like a Lego figure. Such a function can greatly improve your code.

But with great power comes great responsibility. Such a function is a constant danger of SQL injection, through table and field names. You should take care of that. Not to mention it should be properly implemented using prepared statements for the data.

First of all, you will need a general purpose function to execute an arbitrary MySQL query using a query and an array of parameters. I have a simple mysqli helper function for you. It will be a basic function to execute all prepared queries:

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;
}

Now we can start constructing the SQL query dynamically. For this we will need a function that would escape identifiers

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

It will make identifiers safe, at least as long as you are using Unocode.

Now we can proceed to creation of the correct SQL string. We will need to create an SQL with placeholders, like this:

INSERT INTO `staff` (`name`,`occupation`) VALUES (?,?)

So let's write a function that would create a query like this

function create_insert_query($table, $keys)
{
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    return "INSERT INTO $table ($fields) VALUES ($placeholders)";
}

And finally we can write the long-sought crud function:

function crud_insert($conn, $table, $data) {
    $sql = create_insert_query($table, array_keys($data));
    prepared_query($conn, $sql, array_values($data));
}

called like this

$args = ['name' => "D'Artagnan", "occupation" => 'musketeer'];
crud_insert($link, "test_table", $args); 
Saturday, August 21, 2021
 
anky
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share