Learn about prepared statements in MariaDB Server. This section details how to use them for efficient and secure execution of repetitive SQL queries, preventing SQL injection vulnerabilities.
Release a prepared statement to free resources. This command removes the statement definition and its name from the current session.
{DEALLOCATE | DROP} PREPARE stmt_nameTo deallocate a prepared statement produced with PREPARE, use aDEALLOCATE PREPARE statement that refers to the prepared statement name.
A prepared statement is implicitly deallocated when a new PREPARE command is issued. In that case, there is no need to use DEALLOCATE.
Attempting to execute a prepared statement after deallocating it results in an error, as if it was not prepared at all:
If the specified statement has not been PREPAREd, an error similar to the following will be produced:
See .
This page is licensed: GPLv2, originally from
ERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to EXECUTEERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to DEALLOCATE PREPARERun a previously prepared statement. This command executes the statement using the specified name, optionally supplying input parameters.
After preparing a statement with PREPARE, you execute it with anEXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply aUSING clause that lists expressions containing the values to be bound to the parameters. The USING clause must name exactly as many expressions as the number of parameter markers in the statement.
You can execute a given prepared statement multiple times, passing different variables to it or setting the variables to different values before each execution.
If the specified statement has not been PREPAREd, an error similar to the following is produced:
EXECUTE with arbitrary expression as parameters can be used, not just user variables (@var_name).
You can only use user variables (@var_name) as parameters.
See .
This page is licensed: GPLv2, originally from
EXECUTE stmt_name
[USING expression[, expression] ...]ERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to EXECUTEPrepare and run a dynamic SQL statement in one step. This command simplifies the process by combining the PREPARE and EXECUTE operations.
EXECUTE IMMEDIATE statement
[USING param[, param] ...]
param:
expression | IGNORE | DEFAULTEXECUTE IMMEDIATE executes a dynamic SQL statement created on the fly, which can reduce performance overhead. For example:
which is shorthand for:
EXECUTE IMMEDIATE supports complex expressions as prepare source and parameters:
Limitations: subselects and stored function calls are not supported as a prepare source.
The following examples return an error:
One can use a user or an SP variable as a workaround:
EXECUTE IMMEDIATE supports user variables and SP variables as OUT parameters:
Similar to PREPARE, EXECUTE IMMEDIATE is allowed in stored procedures but is not allowed in stored functions.
This example uses EXECUTE IMMEDIATE inside a stored procedure:
This script returns an error:
EXECUTE IMMEDIATE can use DEFAULT and IGNORE indicators as bind parameters:
EXECUTE IMMEDIATE increments the status variable, as well as the , and status variables.
Note, EXECUTE IMMEDIATE does not increment the status variable. Com_execute_sql is used only for ...
This session screenshot demonstrates how EXECUTE IMMEDIATE affects status variables:
This page is licensed: CC BY-SA / Gnu FDL
EXECUTE IMMEDIATE 'SELECT 1'PREPARE stmt FROM "select 1";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', 't1', ' WHERE a=?') USING 5+5;CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN 'SELECT * FROM t1';
EXECUTE IMMEDIATE f1();
ERROR 1970 (42000): EXECUTE IMMEDIATE does not support subqueries or stored functions
EXECUTE IMMEDIATE (SELECT 'SELECT * FROM t1');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near
'SELECT 'SELECT * FROM t1')' at line 1
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING f1();
ERROR 1970 (42000): EXECUTE..USING does not support subqueries or stored functions
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING (SELECT 10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near
'SELECT 10)' at line 1CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN 'SELECT * FROM t1';
SET @stmt=f1();
EXECUTE IMMEDIATE @stmt;
SET @stmt=(SELECT 'SELECT 1');
EXECUTE IMMEDIATE @stmt;
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
SET @param=f1();
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING @param;
SET @param=(SELECT 10);
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING @param;DELIMITER $$
CREATE OR REPLACE PROCEDURE p1(OUT a INT)
BEGIN
SET a:= 10;
END;
$$
DELIMITER ;
SET @a=2;
EXECUTE IMMEDIATE 'CALL p1(?)' USING @a;
SELECT @a;
+------+
| @a |
+------+
| 10 |
+------+DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
EXECUTE IMMEDIATE 'SELECT 1';
END;
$$
DELIMITER ;
CALL p1;
+---+
| 1 |
+---+
| 1 |
+---+DELIMITER $$
CREATE FUNCTION f1() RETURNS INT
BEGIN
EXECUTE IMMEDIATE 'DO 1';
RETURN 1;
END;
$$
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggerCREATE OR REPLACE TABLE t1 (a INT DEFAULT 10);
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING DEFAULT;
SELECT * FROM t1;
+------+
| a |
+------+
| 10 |
+------+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME RLIKE
('COM_(EXECUTE|STMT_PREPARE|STMT_EXECUTE|STMT_CLOSE)');
+-----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------+----------------+
| COM_EXECUTE_IMMEDIATE | 0 |
| COM_EXECUTE_SQL | 0 |
| COM_STMT_CLOSE | 0 |
| COM_STMT_EXECUTE | 0 |
| COM_STMT_PREPARE | 0 |
+-----------------------+----------------+
EXECUTE IMMEDIATE 'SELECT 1';
+---+
| 1 |
+---+
| 1 |
+---+
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME RLIKE
('COM_(EXECUTE|STMT_PREPARE|STMT_EXECUTE|STMT_CLOSE)');
+-----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------+----------------+
| COM_EXECUTE_IMMEDIATE | 1 |
| COM_EXECUTE_SQL | 0 |
| COM_STMT_CLOSE | 1 |
| COM_STMT_EXECUTE | 1 |
| COM_STMT_PREPARE | 1 |
+-----------------------+----------------+Parse and optimize a SQL statement for later use. This command assigns a name to the statement, enabling efficient execution with parameters.
The PREPARE statement prepares a statement and assigns it a name,stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a (not a , an SQL expression or a subquery) that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, "?" characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The "?" characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where expressions should appear, not for SQL keywords, identifiers, and so forth.
The scope of a prepared statement is the session within which it is created. Other sessions cannot see it.
If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.
Prepared statements can be PREPARE and in a stored procedure, but not in a stored function or trigger. Also, even if the statement is prepared with PREPARE in a procedure, it will not be deallocated when the procedure execution ends.
A prepared statement can access , but not or procedure's parameters.
If the prepared statement contains a syntax error, PREPARE will fail. As a side effect, stored procedures can use it to check if a statement is valid. For example:
The and functions, if called immediatly after EXECUTE, return the number of rows read or affected by the prepared statements; however, if they are called after DEALLOCATE PREPARE, they provide information about this statement. If the prepared statement produces errors or warnings, return information about them. DEALLOCATE PREPARE shouldn't clear the , unless it produces an error.
A prepared statement is executed with and released with .
The server system variable determines the number of allowed prepared statements that can be prepared on the server. If it is set to 0, prepared statements are not allowed. If the limit is reached, an error similar to the following will be produced:
In , PREPARE stmt FROM 'SELECT :1, :2' is used, instead of ?.
All statements can be prepared, except , , and .
Prior to this, not all statements can be prepared. Only the following SQL commands are permitted:
Synonyms are not listed here, but can be used. For example, DESC can be used instead of DESCRIBE.
can be prepared too.
Note that if a statement can be run in a stored routine, it will work even if it is called by a prepared statement. For example, can't be directly prepared. However, it is allowed in . If the x() procedure contains SIGNAL, you can still prepare and execute the 'CALL x();' prepared statement.
PREPARE supports most kinds of expressions as well, for example:
When PREPARE is used with a statement which is not supported, the following error is produced:
Since identifiers are not permitted as prepared statements parameters, sometimes it is necessary to dynamically compose an SQL statement. This technique is called dynamic SQL). The following example shows how to use dynamic SQL:
Use of variables in prepared statements:
This page is licensed: GPLv2, originally from
PREPARE stmt_name FROM preparable_stmtFLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES | QUERY CACHE | TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS}
RESET {MASTER | SLAVE | QUERY CACHE}
SHOW {DATABASES | TABLES | OPEN TABLES | TABLE STATUS | COLUMNS | INDEX | TRIGGERS |EVENTS | GRANTS | CHARACTER SET | COLLATION | ENGINES | [PLUGINS SONAME] | PRIVILEGES |PROCESSLIST | PROFILE | PROFILES | VARIABLES | STATUS | WARNINGS | ERRORS |TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS | AUTHORS |CONTRIBUTORS}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
UNINSTALL {PLUGIN | SONAME}
CREATE PROCEDURE `test_stmt`(IN sql_text TEXT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT CONCAT(sql_text, ' is not valid');
END;
SET @SQL := sql_text;
PREPARE stmt FROM @SQL;
DEALLOCATE PREPARE stmt;
END;ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements
(current value: 0)PREPARE stmt FROM CONCAT('SELECT * FROM ', table_name);ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yetCREATE TABLE t1 (a INT,b CHAR(10));
INSERT INTO t1 VALUES (1,"one"),(2, "two"),(3,"three");
PREPARE test FROM "select * from t1 where a=?";
SET @param=2;
EXECUTE test USING @param;
+------+------+
| a | b |
+------+------+
| 2 | two |
+------+------+
SET @param=3;
EXECUTE test USING @param;
+------+-------+
| a | b |
+------+-------+
| 3 | three |
+------+-------+
DEALLOCATE PREPARE test;CREATE PROCEDURE test.stmt_test(IN tab_name VARCHAR(64))
BEGIN
SET @sql = CONCAT('SELECT COUNT(*) FROM ', tab_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
CALL test.stmt_test('mysql.user');
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+PREPARE stmt FROM 'SELECT @x;';
SET @x = 1;
EXECUTE stmt;
+------+
| @x |
+------+
| 1 |
+------+
SET @x = 0;
EXECUTE stmt;
+------+
| @x |
+------+
| 0 |
+------+
DEALLOCATE PREPARE stmt;