Database Reference
In-Depth Information
CALL
exec_stmt
(
CONCAT
(
'CREATE TABLE '
,
@
tbl_name
,
' (i INT)'
));
CALL
exec_stmt
(
CONCAT
(
'INSERT INTO '
,
@
tbl_name
,
' (i) VALUES('
,
@
val
,
')'
));
exec_stmt()
uses an intermediary user-defined variable,
@_exec_stmt
, because
PRE
PARE
accepts a statement only when specified using either a literal string or a user-
defined variable. A statement stored in a routine parameter does not work. (Avoid using
@_exec_stmt
for your own purposes, at least if you expect its value to persist across
exec_stmt()
invocations.)
Now, how about making it safer to construct statement strings that incorporate values
that might come from external sources, such as web-form input or command-line ar‐
guments? Such information cannot be trusted and should be treated as a potential SQL
injection attack vector:
• The
QUOTE()
function is available for quoting data values.
• There is no corresponding function for identifiers, but it's easy to write one that
doubles internal backticks and adds a backtick at the beginning and end:
CREATE
FUNCTION
quote_identifier
(
id
TEXT
)
RETURNS
TEXT
DETERMINISTIC
RETURN
CONCAT
(
'`'
,
REPLACE
(
id
,
'`'
,
'``'
),
'`'
);
Revising the preceding example to ensure the safety of data values and identifiers, we
have:
SET
@
tbl_name
=
quote_identifier
(
@
tbl_name
);
SET
@
val
=
QUOTE
(
@
val
);
CALL
exec_stmt
(
CONCAT
(
'CREATE TABLE '
,
@
tbl_name
,
' (i INT)'
));
CALL
exec_stmt
(
CONCAT
(
'INSERT INTO '
,
@
tbl_name
,
' (i) VALUES('
,
@
val
,
')'
));
A constraint on use of
exec_stmt()
is that not all SQL statements are eligible for exe‐
cution as prepared statements. See the
MySQL Reference Manual
for the limitations.
9.10. Handling Errors Within Stored Programs
Within stored programs, you can catch errors or exceptional conditions using condition
handlers. A handler activates under specific circumstances, causing the code associated
with it to execute. The code takes suitable action such as performing cleanup processing
or setting a variable that can be tested elsewhere in the program to determine whether
the condition occurred. A handler might even ignore an error if it occurs under certain
permitted conditions and you want to catch it rather than have it terminate your pro‐
gram.
Stored programs can also produce their own errors or warnings to signal that something
has gone wrong.
