Many more mature databases support the concept of prepared statements.
What are prepared statements? Think of it as a compiled template of the SQL you want to run, which can be customized using variable parameters. Prepared statements can bring two major benefits:
A query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When a query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. This process takes longer for complex queries and can significantly slow down your application if the same query needs to be repeated multiple times with different parameters. By using prepared statements, you can avoid repeated analysis/compile/optimization cycles. Simply put, prepared statements use fewer resources and therefore run faster.
Parameters provided to prepared statements do not need to be enclosed in quotes; the driver handles this automatically. If your application uses only prepared statements, you can be sure that SQL injection will not occur. (However, if other parts of the query are constructed from unescaped input, there is still a risk of SQL injection).
Prepared statements are so useful that their only feature is that PDO will simulate processing when the driver does not support it. This ensures that applications can use the same data access pattern regardless of whether the database has such capabilities.
The following example executes an insert query by replacing the corresponding named placeholders with name and value.
<?php$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");$stmt->bindParam(':name', $name);$stmt- >bindParam(':value', $value);//Insert a row $name = 'one';$value = 1;$stmt->execute();// Insert another row with different values $name = 'two';$value = 2;$stmt->execute();?>The following example executes an insert query by replacing the ? placeholder with name and value.
<?php$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");$stmt->bindParam(1, $name);$stmt->bindParam(2, $value);//Insert a row $name = 'one';$value = 1;$stmt->execute();// Insert another row with different values $name = 'two';$value = 2;$stmt->execute();?>The following example obtains data based on the key value in the provided form. User input is automatically quoted, so there is no danger of SQL injection attacks.
<?php$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");if ($stmt->execute(array($_GET['name']))) { while ($row = $stmt->fetch()) { print_r($row); }}?>If the database driver supports it, the application can also bind output and input parameters. Output parameters are often used to obtain values from stored procedures. Output parameters are slightly more complicated to use than input parameters because when binding an output parameter, you must know the length of the given parameter. If the value bound to a parameter is greater than the recommended length, an error is generated.
<?php$stmt = $dbh->prepare("CALL sp_returns_string(?)");$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); // Call the stored procedure $stmt->execute ();print "procedure returned $return_valuen";?>You can also specify parameters that have both input and output values, with syntax similar to output parameters. In the next example, the string "hello" is passed to the stored procedure, and when the stored procedure returns, hello is replaced with the value returned by the stored procedure.
<?php$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");$value = 'hello';$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000 ); // Call the stored procedure $stmt->execute();print "procedure returned $valuen";?> <?php$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");$stmt->execute(array($_GET['name']));// Placeholder symbol must be used throughout the value $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");$stmt->execute(array("%$_GET[name]%"));?>