I'm trying to build a dynamic query based upon selections passed to a script. Example:
$qry = "SELECT * FROM machinekaart
INNER JOIN afleveradressen ON afleveradressen.raaid = mkrraaid
INNER JOIN kontaktpersonen ON kontaktpersonen.rkpraaid = mkrraaid
WHERE mkrrid != '' " ;
if($_SESSION['oud'])
$qry .= " AND mkrvo < " . $cur_jaar_maand;
Field mkrvo is a text field, and can contain yyyy-mm besides other values.
e.g. when the varable $cur_maand_jaar contains '2015-01' the selection will be everything lower than 2014
How can I stop this from happening and selecting everything lower than '2015-01' ??
I would suggest quoting that variable, so the values are taken literally:
if($_SESSION['oud'])
$qry .= " AND mkrvo < '" . $cur_jaar_maand . "'";
Better than that, please use PDO so you can use bindings, it's safer and best optimized.
Eg.
if($_SESSION['oud'])
$qry .= " AND mkrvo < ?";
// build your PDO Connection $myPdoConnection ...
$pdoStatement = $myPdoConnection->prepare($qry);
$pdoStatement->execute(array($cur_jaar_maand));
Within the SQL text, enclose the string literal in single quotes, so it's not evaluated as a numeric expression.
Evaluated in a numeric context: 2015-01 produces a value of 2014.
But '2015-01' is evaluated as a string literal.
(If the string literal is evaluated in a numeric context (e.g. '2015-01' + 0) the string will evaluate to a numeric value of 2015.)
The code you posted appears to be vulnerable to SQL Injection.
Consider what SQL text is generated when $cur_jaar_maand happens to evaluate to 0 OR 1=1 --.
A much better pattern is to make use of prepared statements with bind placeholders.