Database Reference
In-Depth Information
SELECT
*
FROM
profile
LIMIT
5
+
5
;
SELECT
*
FROM
profile
LIMIT
@
skip_count
,
@
show_count
;
The same “no expressions permitted” principle applies if you use an expression to cal‐
culate a
LIMIT
value in a program that constructs a statement string. You must evaluate
the expression first, and then place the resulting value in the statement. For example, if
you produce a statement string in Perl or PHP as follows, an error will result when you
attempt to execute the statement:
$str
=
"SELECT * FROM profile LIMIT $x + $y"
;
To avoid the problem, evaluate the expression first:
$z
=
$x
+
$y
;
$str
=
"SELECT * FROM profile LIMIT $z"
;
Or do this (don't omit the parentheses or the expression won't evaluate properly):
$str
=
"SELECT * FROM profile LIMIT "
.
(
$x
+
$y
);
To construct a two-argument
LIMIT
clause, evaluate both expressions before placing
them into the statement string.
Another issue related to
LIMIT
(or other syntax constructions that require literal integer
values) occurs when you use prepared statements from an API that quotes all data values
as strings when binding them to parameter markers. Suppose that you prepare and
execute a statement like this in PDO:
$sth
=
$dbh
->
prepare
(
"SELECT * FROM profile LIMIT ?,?"
);
$sth
->
execute
(
array
(
2
,
4
));
To resulting statement is as follows, with quoted
LIMIT
arguments, so statement exe‐
cution fails:
SELECT
*
FROM
profile
LIMIT
'2'
,
'4'
To avoid this problem, evaluate the
LIMIT
arguments and place them in the statement
yourself, as just described. Alternatively, if your API has type-hinting capability, use it
to indicate that the
LIMIT
arguments are integers to prevent them from being quoted.
