未加星标

A Guide to MySQL Prepared Statements and Parameterized Queries

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

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.

Related Articles Applying MySQL’s GROUP_CONCAT Function to Multiple Columns Why You Should be Using the MySQL GROUP_CONCAT Function Listing Duplicate Values by Group Top 6 MySQL DBA Mistakes Selecting the Top N Results by Group in MySQL

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:

PREPARE: prepares the statement for execution. EXECUTE: executes the prepared statement. 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

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:A Guide to MySQL Prepared Statements and Parameterized Queries
本站链接:https://www.codesec.net/view/610709.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(85)