Asked  2 Years ago    Answers:  5   Viewed   61 times

The classic transactions in a loop code:

$mysqli->query("START TRANSACTION");
foreach ($pdata as $key => $value) {
    $sql    = "INSERT INTO temp (`fund_id`) VALUES (" . $value . ")";
    $result = $mysqli->query($sql);
}
$mysqli->query("COMMIT");

Then we change to prepared statements:

$mysqli->autocommit(FALSE);
foreach ($pdata as $key => $value) {
    $sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $value);
    $stmt->execute();
}
$mysqli->commit();

Questions:

1) Are these two codes identical? Am I missing something in the second code with prepared statements?

2) Is $mysqli->commit() the same as $mysqli->query("COMMIT")?

3) Do I need to add $mysqli->query("START TRANSACTION"); for the prepared statements block or the transaction will automatically start when we set autocommit(FALSE)?

 Answers

4

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.

Thursday, November 10, 2022
2

Prepared statements and transactions are unrelated techniques and technologies.

You may wish to issue the START TRANSACTION and COMMIT/ROLLBACK commands directly instead of using the dedicated methods. They are functionally equivalent.

For your loop, you'd issue the START TRANSACTION before your prepare, then your COMMIT after the loop exits. You probably should not try to open a transaction after a prepared statement has been started but before it's been executed.

For some reason, they didn't add a "start transaction" command in favor of turning off autocommit. It's one of those weird things about mysqli that makes me always recommend PDO instead. :) Opening a transaction implicitly turns off autocommit for the duration of the transaction.

Friday, August 5, 2022
 
mhmd
 
1

when you await a Task it first checks to see if the task is complete, if it is complete it just continues the execution and never returns to the caller. Because of this the call to await DoWork(); will never cause you to return to the calling method, it will just synchronously continue on in the method.

When you remove the delay you now have the equivalent of having

public static async Task Loop()
{
    while(true)
    {
    }
}

so the loop will loop forever without ever giving control back to the caller. In situations like this where you don't know if you will be returning to the caller or not and you want to guarantee you don't loop forever you could rewrite your code as

public static async Task Loop()
{
    while(true)
    {
        var workTask = DoWork();
        if(workTask.GetAwaiter().IsCompleted) //This IsCompleted property is the thing that determines if the code will be synchronous.
            await Task.Yield(); //If we where syncronous force a return here via the yield.
        await workTask; //We still await the task here in case where where not complete, also to observe any exceptions.
    }
}
Wednesday, November 2, 2022
 
3

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Monday, September 12, 2022
 
2

You always need to use white-lists for stuff like table- or column names, whether you use prepared statements or the mysql escape functions.

The problem is that table names and column names are not quoted in single or double quotes, so if you use a function that specifically quotes these characters (and some more of course...), it will do nothing for your table name.

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table. Nothing in this string will get escaped by mysql's escape functions but it is definitely a string you would want to check against a white-list.

Apart from that the mysql escape functions have a problem with character sets that can render them useless, so you are always better off with prepared statements.

Tuesday, December 6, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share

Browse Other Code Languages