How can I use prepared statements combined with Transactions with PHP?

Asked by Preston Robbins on
Tags:
8 Answers

Answer by Colt Yates

I would like to incorporate the query as one would with a prepared statement:,My goal is to use a transaction and a prepared statement simultaneously, to achieve both integrity of data, and prevention of SQL injection.,Connect and share knowledge within a single location that is structured and easy to search., Student asked me if it is necessary to simplify fractions at the end of answering a question. I'm not sure how to respond

try 
{
    $cnx = new PDO ($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction ();

    $stmt = $cnx->prepare ("SELECT * FROM users WHERE username=?");
    $stmt->execute(array($username));

    $cnx->commit();

    while ($row = $stmt->fetch (PDO::FETCH_OBJ)){
        echo $row->userid;
    }
}

catch (Exception $e) { 
    if (isset ($cnx)) 
        $cnx->rollback ();
       echo "Error:  " . $e; 
    }
}

Source: https://stackoverflow.com/questions/10642635/how-can-i-use-prepared-statements-combined-with-transactions-with-php


Answer by Willie Liu

PHP Parse error: syntax error, unexpected T_CATCH,My goal is to use a transaction and a prepared statement simultaneously, to achieve both integrity of data, and prevention of SQL injection.,I would like to incorporate the query as one would with a prepared statement:,PS: 1) I'm assuming, of course, that $user_input and $user_input_2 are available immediately. You don't want your transaction hanging open unnecessarily long ;)

I have this:

   try {
        $cnx = new PDO($dsn,$dbuser,$dbpass);   
        $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $cnx->beginTransaction();
        $cnx->query("SELECT * FROM users WHERE username=$escaped_input");
        $cnx->query("SELECT * FROM othertable WHERE some_column=$escaped_input_2");

        $cnx->commit();
    }

    catch (Exception $e){
           $cxn->rollback();
           echo "an error has occured";

    }

I would like to incorporate the query as one would with a prepared statement:

$stmt=$cxn->prepare("SELECT * FROM users WHERE username=?");
$stmt->execute(array($user_input));

$stmt_2=$cxn->prepare("SELECT * FROM othertable WHERE some_column=?");
$stmt_2->execute(array($user_input_2));

Here is my updated code:

try 
{
    $cnx = new PDO($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction();
    $stmt=$cnx->prepare("SELECT * FROM users WHERE username=?");
    $stmt->execute(array($username));

    $cnx->commit();

    while ($row=$stmt->fetch(PDO::FETCH_OBJ)){
    echo $stmt->userid;

}

catch(Exception $e) { 
    if (isset($cnx)) 
        $cnx->rollback();
       echo "Error:  " . $e; 
    }

Source: https://www.py4u.net/discuss/790432


Answer by Casey Harper

I started looking at transactions, and it seems no examples or scripts I find on the net uses prepared statements and transactions together. All examples uses mysqli->query() and perhaps the real_escape_string() which to me feels old-school.,I do not see what would be wrong with using prepared statements with transactions since they are orthogonal in use.,In relation to the precise question here; laserlight's reply covers it: it's feasible to use transactions and prepared statements together, or use one but not the other, or use neither. They address different aspects of the process of interacting with the database.,I use prepared statements all the time but lately I've been thinking this may not be the intended way. I do pretty simple db queries and never really reuse a prepared statement more than once or twice in the same script.

I was thinking something like this:

$stmt = $mysqli->prepare("INSERT INTO table1 ............");
		$stmt->bind_param("ii", $var1, $var2, .....);
		$stmt->execute();
		$stmt->close();

	if($mysqli->sqlstate != "00000")
		$mysqli->rollback();

$stmt = $mysqli->prepare(" INSERT INTO table2.....)";

Source: https://board.phpbuilder.com/d/10364768-resolved-should-you-use-mysqli-prepared-statements-and-transactions-together


Answer by Dorothy Rogers

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections. , Prepared Statements , A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again. ,Example #1 Prepared statement

array(3) {
  [0]=>
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}

Source: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php


Answer by Nathanael Velazquez

Inserting, updating and deleting have an identical syntax, so they will be combined.,In plain English, this is how MySQLi prepared statements work in PHP:, Multiple Prepared Statements in Transactions ,Multiple Prepared Statements in Transactions

In a normal MySQL call, you would do something like:

$name = $_POST['name'];
$mysqli->query("SELECT * FROM myTable WHERE name='$name'");

The problem with this, is that if it is based off of user input, like in the example, then a malicious user could do ' OR '1'='1. Now this statement will always evaluate to true, since 1=1. In this case, the malicious user now has access to your entire table. Just imagine what could happen if it were a DELETE query instead. Take a look at what is actually happening to the statement.

SELECT * FROM myTable WHERE name='' OR '1'='1' 

A hacker could do a lot of damage to your site if your queries are set up like this. An easy fix to this would be to do:

$name = $mysqli->real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM myTable WHERE name='$name'");

A prepared statement, as its name implies, is a way of preparing the MySQL call, without storing the variables. You tell it that variables will go there eventually — just not yet. The best way to demonstrate it is by example.

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ? AND age = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close();

Creating a new MySQLi is pretty simple. I suggest naming a file called mysqli_connect.php and place this file outside of your root directly (html, public_html) so your credentials are secure. We'll also be using exception handling, by utilizing mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). This might look weird to you, especially if you've never used a bitwise operator before. But all it's doing is reporting all errors, while converting them to exceptions, using the mysqli_sql_exception class.

$mysqli = new mysqli("localhost", "username", "password", "databaseName");
if($mysqli->connect_error) {
  exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");

Source: https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection


Answer by Ember Sweeney

The following shows how to use the prepared statement to insert a new row into the authors table:,The following example shows how to insert the author Nick Abadzis into the authors table using a bound statement:,The following example illustrates how to use the bindParam() method to insert a new author into the authors table:,Summary: in this tutorial, you will learn about the PHP prepared statements in PDO and how to use them effectively.

First, create a template SQL statement. For example:

.wp-block-code {
	border: 0;
	padding: 0;
}

.wp-block-code > div {
	overflow: auto;
}

.shcb-language {
	border: 0;
	clip: rect(1px, 1px, 1px, 1px);
	-webkit-clip-path: inset(50%);
	clip-path: inset(50%);
	height: 1px;
	margin: -1px;
	overflow: hidden;
	padding: 0;
	position: absolute;
	width: 1px;
	word-wrap: normal;
	word-break: normal;
}

.hljs {
	box-sizing: border-box;
}

.hljs.shcb-code-table {
	display: table;
	width: 100%;
}

.hljs.shcb-code-table > .shcb-loc {
	color: inherit;
	display: table-row;
	width: 100%;
}

.hljs.shcb-code-table .shcb-loc > span {
	display: table-cell;
}

.wp-block-code code.hljs:not(.shcb-wrap-lines) {
	white-space: pre;
}

.wp-block-code code.hljs.shcb-wrap-lines {
	white-space: pre-wrap;
}

.hljs.shcb-line-numbers {
	border-spacing: 0;
	counter-reset: line;
}

.hljs.shcb-line-numbers > .shcb-loc {
	counter-increment: line;
}

.hljs.shcb-line-numbers .shcb-loc > span {
	padding-left: 0.75em;
}

.hljs.shcb-line-numbers .shcb-loc::before {
	border-right: 1px solid #ddd;
	content: counter(line);
	display: table-cell;
	padding: 0 0.75em;
	text-align: right;
	-webkit-user-select: none;
	-moz-user-select: none;
	-ms-user-select: none;
	user-select: none;
	white-space: nowrap;
	width: 1%;
}$sql = 'insert into authors(first_name, last_name)
        values(?,?)';Code language: PHP (php)

Second, call the prepare() method of a PDO instance:

$statement = $pdo->prepare($sql);Code language: PHP (php)

Third, call the execute() method and pass the values to the placeholders:

$statement->execute(['Sandra', 'Aamodt']);Code language: PHP (php)

The following shows how to use the prepared statement to insert a new row into the authors table:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(?,?)';

$statement = $pdo->prepare($sql);

$statement->execute(['Sandra', 'Aamodt']);Code language: HTML, XML (xml)

If an SQL statement has many placeholders, it’s quite easy to use the wrong positions. To avoid this, you can use the named placeholders instead. For example:

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';Code language: PHP (php)

When executing the statement, you need to pass an associative array to the execute() method like this:

$statement->execute([
	'first_name' => 'Henry',
	'last_name' => 'Aaron'
]);Code language: PHP (php)

Note that the key of the array is important, not the order of elements. Also, you can optionally use the : in the keys of the array:

$statement->execute([
	':first_name' => 'Henry',
	':last_name' => 'Aaron'
]);Code language: PHP (php)

The order of the array element is not important so you can use an array with elements in any order. For example:

$statement->execute([
	'last_name' => 'Aaron',
	'first_name' => 'Henry',
]);Code language: PHP (php)

Put it all together.

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$statement->execute([
	'last_name' => 'Aaron',
	'first_name' => 'Henry',
]);Code language: HTML, XML (xml)

To bind a value, you use the bindValue() method of the PDOStatement object:

public PDOStatement::bindValue ( mixed $parameter , mixed $value , int $data_type = PDO::PARAM_STR ) : boolCode language: PHP (php)

The following example shows how to insert the author Nick Abadzis into the authors table using a bound statement:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(?,?)';

$statement = $pdo->prepare($sql);

$statement->bindValue(':first_name', 'Nick');
$statement->bindValue(':last_name', 'Abadzis');

$statement->execute();Code language: HTML, XML (xml)

When you use the bindValue() method, the execute() method executes the statement with the values passed to the bindValue() method, not the values at the time the execute() method runs. For example:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Chris',
	'last_name' => 'Abani',
];

$statement->bindValue(':first_name', $author['first_name']);
$statement->bindValue(':last_name', $author['last_name']);

// change the author variable
$author['first_name'] = 'Tom';
$author['last_name'] = 'Abate';

// execute the query with value Chris Abani
$statement->execute();Code language: HTML, XML (xml)

To execute a statement whose values of the parameters are evaluated at the time the execute() method runs, you use the bindParam() method:

public PDOStatement::bindParam ( mixed $parameter , mixed &$variable , int $data_type = PDO::PARAM_STR , int $length = ? , mixed $driver_options = ? ) : boolCode language: PHP (php)

The following example illustrates how to use the bindParam() method to insert a new author into the authors table:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Chris',
	'last_name' => 'Abani',
];

$statement->bindParam(':first_name', $author['first_name']);
$statement->bindParam(':last_name', $author['last_name']);

// change the author variable
$author['first_name'] = 'Tom';
$author['last_name'] = 'Abate';

// execute the query with value Tom Abate
$statement->execute();Code language: HTML, XML (xml)

Source: https://www.phptutorial.net/php-pdo/php-prepared-statement/


Answer by Coraline Klein

Is it possible to combine transactions with the exapmple of PDO Wrapper?,In both cases you can just iterate over the statement. In case there is no result, then there simply will be no output:,Prepared statements and IN clause,security (usable prepared statements)

When only one row is expected - to get that only row. For example,

$row = $stmt->fetch(PDO::FETCH_ASSOC);

Source: https://phpdelusions.net/pdo


Answer by Celine Fuller

The ability to perform transactions depends upon the type of database storage engine in use. To perform transactions in MySQL, the InnoDB storage engine must be used. Transasctions in PHP use the following functions – mysqli_begin_transaction(), mysqli_commit(), and mysqli_rollback().,Chapter 9 - DB Access Using MySQLMySQL FunctionsInserting RecordsSelecting RecordsDeleting RecordsUpdating RecordsTransactionsPrepared Statements ,If you are executing a single query or a series of single queries using the standard mysqli_query() function, it is also possible to retrieve the auto increment value using the built-in PHP function mysqli_insert_id().,Suppose we have a web form that collects user address data that will be spread over two tables – First Name, and Last Name will be stored in Customer table, while address values for the person will be stored in an Address table. The ERD below illustrates the one-to-many relationship. Each customer can have many addresses. The tables are linked using the customer id value (cid) which is a MySQL auto generated primary key value in the Customer table and a foreign key value in the Address table.

<?php
        
if (isset($_POST['submitb'])) 
    {
    
    $firstName = $_POST['FirstName'];
    $lastName = $_POST['LastName'];
    $address = $_POST['Address'];
    $city = $_POST['City'];
    $state = $_POST['State'];
               
     //Establish a connection to the Database
//Reference the mysqli_connection.php file created above

     include('mysqli_connection.php');

 

    //Begin Transaction

       mysqli_begin_transaction($conn);  

$sqlInsertCustomer = "INSERT INTO Customer (first_name,last_name) VALUES ('$firstName','$lastName')";

$sqlInsertAddress = "INSERT INTO Address (address,city,state,cid) VALUES   ('$address','$city',’$state’,LAST_INSERT_ID())";

//Build a Single SQL statement

       $query = $sqlInsertCustomer ."; " . $sqlInsertAddress;

$count = 0;

   if(mysqli_multi_query($conn,$query)){
do{

        $count+=mysqli_affected_rows($conn);

       }while(mysqli_more_results($conn) && mysqli_next_result($conn));
}

//IF BOTH RECORDS ARE INSERTED, COMMIT THE TRANSACTION. OTHERWISE, ROLLBACK

   if ($count == 2) {

   mysqli_commit($conn);

   }

  else {  

mysqli_rollback($conn);

  }

     mysqli_close($conn);
               
    }
        
?>
 

Source: https://itwebtutorials.mga.edu/php/chp9/transactions.aspx


Related