Combine prepared transactions and queries

Question

Combine prepared transactions and queries

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I am studying the subject of transactions and queries prepared in PHP. Testing I have created a code to combine transactions with prepared queries. It would be something like that (imagine that the connection is already established by PDO):

try {

// 1º Preparo la sentencia/as
$queryInsert = "INSERT INTO 'usuarios' ('idusuario', 'nombreusuario', 'emailusuario', 'passusuario') VALUES (NULL, ?, 'afasf@2224', ?)";
$queryDelete = "DELETE FROM 'usuarios' WHERE 'usuarios'.'idusuario' = ?";

$conexion->beginTransaction(); //2º Comenzamos la transaccion

$PDOstmt = $conexion->prepare($queryInsert); // 3º Preparo la sentencia primera

$nombreUsuario = "nanaki"; //4º Preparo los valores que enlazare con la sentencia
$passUsuario = "final fantasy 7";

$PDOstmt->execute(array($nombreUsuario, $passUsuario)); // 5º ejecuto

//Repito los puntos 3º, 4º y 5º...

$PDOstmt=$conexion->prepare($queryDelete);

$idusuario=13;

$PDOstmt->execute(array($idusuario));

//...Tantas sentencias como tengamos

$conexion->commit();//6º confirmo cambios...

echo "Usuario actualizado";
$PDOstmt->closeCursor();//Cerramos cursor
$conexion = null;
} catch (PDOException $exc) { //Capturo cualquier error

$conexion->rollback();//... o los revierto si hemos capturado algun error

echo "No se ha podido insertar usuario, error: " . $exc->getMessage();

exit();
}

The script works. What I wanted to know is if this way of doing it and of combining transactions and prepared consultations would be correct.

Greetings.

php prepared-statements transacciones

asked by Carlos Rayón Álvarez 01.02.2018 в 01:48

source

3 answers

2

For transactions in PDO, the following must be taken into account:

Let the tables be in InnoDB . Transactions do not work in tables MyISAM

That the PDO object that you use has the error mode correctly configured, having PDO::ERRMODE_EXCEPTION established when creating the connection or establishing it after having created it and before proceeding with the transactions. For example: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In catch , instead of capturing only PDOException , you must capture any exception, putting Exception instead of PDOException . Suppose you are reading the data to insert from a file and there is a failure to read that file, this failure will not be notified as a PDOException , the code will not enter the catch and the rollBack() can not be made.

If you are going to use throw , you should do it after rollBack() , never before him.

You must ensure that in the transaction queries there are no DDL (Data Definition Language) statements that define or modify the schema of the database, since these queries will generate an commit implicit. For more details on this you can check the documentation .

answered by 01.02.2018 в 15:33

0

My thanks for the answers, I learn a lot with the answers you give me. I go in parts. For the connection as you indicate if I have configured the error mode (at least I think) this would be the code I use for the connection pdo.

function conexion()
{
//Variables para la conexion
$host = "localhost";
$nombreBaseDatos = "pruebas";
$password = "";
$usuario = "root";
try{     
$conexion=new PDO("mysql:host=$host;dbname=$nombreBaseDatos",$usuario,$password); 
$conexion->exec("SET CHARACTER SET utf8");
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conexion;
} catch (Exception $ex) {
die("error" .$ex->getMessage());
}
};

The tables I use if they are InnoDB.

I'm aiming to capture any exception type using Exception instead of PDOException.

Huskie, I also point out the variables in lowercase and the backup of the database. In this case it is only test and example and I do not have any history, but I sign it. For the hash I tried to use md5. In this case the test being does not influence me much either. Thanks Huskie.

I will continue to try and learn.

Greetings to all.

answered by 03.02.2018 в 02:20

Xamarin Geolocator does not return anything to me Django CORS 'Access-Control-Allow-Origin'

score 1 · Accepted Answer

Because of the handling of PDO and prepared statements I think the implementation is correct, however, I have noticed several things in your code and I would like to give you some suggestions to improve it in general:

A) Avoid the combined use of uppercase and lowercase in php variables. It would be better if you use only lowercase for the variables for example. This is because the combination of uppercase and lowercase letters is used very frequently in the declaration of functions and implementation of classes and objects. So it generates a certain "noise" that you have regular variables written in that way.

Example: $PDOstmt would be better so $pdostmt

B) Regarding this line of code:

$queryDelete = "DELETE FROM 'usuarios' WHERE 'usuarios'.'idusuario' = ?";

I do not know if you have any "historical" table or daily backup database but I can still tell you that it is not good practice to delete data > "Sensitive" directly from a database . It is better to have a "status" field in the database that indicates whether the user in question is active or not.

$queryDelete = "Update 'usuarios' SET estatus_usuario=? WHERE 'usuarios'.'idusuario' = ? ";

user_state could be a character of length 1 that contains:

"A" To indicate that the user is active

"E" To indicate that the user is inactive or "deleted"

"B" To indicate that the user is banned (for a certain period or perhaps permanently)

Updating the record in the table instead of actually eliminating it is done by two things:

1) Normally if a hack occurs in the user's account "x" this user will require technical support attention and it will be very difficult to try to recover an account from which the data has been deleted. users of the database.

2) What happens if a user deletes their "account" and wants to resume it later? for example in 1 or 2 months after the "eliminated" (it happens more than you imagine), in this case you are denying the opportunity to the user to retake the game and you are denying the possibility of it becoming a "client" again

C) I would recommend using a hash for user passwords (when saving them in the bd etc) since this "shield" strong> your system a bit better against any possible "attack" to get sensitive data.

I hope you find my observations useful. A greeting!