dcsimg
 

A Guide to MySQL Prepared Statements and Parameterized Queries

Monday Nov 5th 2018 by Rob Gravelle

A prepared statement, or parameterized query, is used to execute the same statement repeatedly in an extremely efficient manner. In today's article, Rob Gravelle demonstrates how to use prepared statements within a stored procedure as well as using a programming language.

Application developers often write code that interacts with a database using parameters supplied by application users. These parameters may include credentials, resource identifiers and other application-specific data. MySQL implements prepared statements for this purpose. A prepared statement, or parameterized query, is used to execute the same statement repeatedly in an extremely efficient manner.

Prepared statements/parameterized queries offer the following major benefits:

  • Less overhead for parsing the statement each time it is executed.
    Typically, database applications process large volumes of nearly-identical statements, with only changes to literal or variable values in clauses, such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.
  • Protection against SQL injection attacks.
    The parameter values can contain unescaped SQL quote and delimiter characters.

In today's article we'll learn how to use prepared statements within a stored procedure as well as using a programming language.

About Prepared Statements

Since version 4.1, MySQL has supported server-side prepared statements, which utilize the enhanced binary client-server protocol. The binary protocol is much more efficient for transmitting client-server data than the textual format protocol. This improves network performance as binary data has smaller byte size than ASCII text.

In prepared statements, certain values are left unspecified, called parameters (labeled "?"). For example: INSERT INTO users VALUES(?, ?, ?). At a later time, the application binds the values to the parameters, and the database executes the statement. The application may re-execute the statement any number of times with different values.

Here's a prepared statement that computes the hypotenuse of a triangle given the lengths of the two sides:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+ --  --  --  --  --  -- +
| hypotenuse |
+ --  --  --  --  --  -- +
|          5 |
+ --  --  --  --  --  -- +
mysql> DEALLOCATE PREPARE stmt1;

Although prepared statements are practically never created via the command line, the above example does illustrate the three statements involved in running a prepared statement. They are:

  1. PREPARE: prepares the statement for execution.
  2. EXECUTE: executes the prepared statement.
  3. DEALLOCATE PREPARE: releases the prepared statement.

Using Prepared Statements within Stored Procedures

One of the best uses of prepared statements is to generate dynamic SQL. Imagine that you need to pass a table name as a parameter value. You can use Dynamic SQL to construct the SQL statement:

CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `SelectAllFromTable`(IN table_name VARCHAR(255))
BEGIN
  PREPARE dynamic_statement FROM 'SELECT * FROM ?';
  EXECUTE dynamic_statement USING @table_name;
  DEALLOCATE PREPARE dynamic_statement;
END

Dynamically Building SQL based on Parameters

The fact that the PREPARE statement command accepts a string opens up a whole world of possibilities. We can construct an SQL statement based on input parameters that can include table and column names, or even sorting order, as evidenced by the following example:

CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

Dynamic queries can get fairly complex. A while back, in my MySQL Prepared Statements to Generate Crosstab SQL tutorial, I employed the CONCAT() and GROUP_CONCAT() string functions to dynamically generate a crosstab SQL statement with a variable number of columns. I even included tab and newline characters so that the finished statement would be easily readable!

Guarding Against SQL Injection

SQL injection is one of the most common web hacking techniques and involves the placement of malicious code within SQL statements, via web page or application input. Observe the following innocuous ASP.net code:

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

Now, just imagine if a sly user enters a value of "105 OR 1=1". Since "OR 1=1" always evaluates to TRUE, the statement would fetch all users from the database.

Unfortunately, it gets worse.

Most databases support batched SQL statements, which consist of a group of two or more SQL statements, separated by semicolons. That would allow a hacker to delete the users table by supplying a value of "105; DROP TABLE users"!

Some inexperienced programmers have tried to sanitize user inputs themselves. Don't go there. Instead, rely on prepared statements to clean up any query parameters that come in from external sources. Before the SQL engine compiles a prepared statement, it first checks each parameter to ensure that it is correct for its column and is treated literally, and not as part of the SQL to be executed.

Here's the above code again done properly, i.e. using a parameterized query:

txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Users WHERE UserId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();

Much better!

MySQL Connectors for Every Language

MySQL Connectors provide connectivity to the MySQL server for client programs. APIs then furnish low-level access to the MySQL protocol and MySQL resources. Both Connectors and the APIs enable you to connect and execute MySQL statements from another language or environment, including ODBC, Java (JDBC), Perl, Python, PHP, Ruby, and native C MySQL instances.

Here's the same query again using the MySQL Connector for PHP:

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM Users WHERE UserId = ?"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind */
$id = 100;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

/* Prepared statement, stage 3: execute */
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

Conclusion

As we saw here today, MySQL Prepared Statements and Parameterized Queries offer some very significant benefits. Moreover, they allow you to write dynamic SQL statements using a variety of external programming languages, thanks to the many MySQL Connector APIs.

See all articles by Rob Gravelle

Home
Mobile Site | Full Site