Reputation: 2567
Currently I'm developing a search form so my SQL query needs to change with user input. Please see the below code sample.
$sqlSearch = "SELECT * FROM seafarers WHERE ";
if ($dateS != "") {
$sqlSearch .= "add_date = '" . changeDateSlashToHypen($dateS) . "' and ";
}
if ($cdcS != "") {
$sqlSearch .= "cdc = '" . $cdcS . "' and ";
}
if ($ppS != "") {
$sqlSearch .= "passport LIKE '%$ppS%' and ";
}
if ($surnameS != "") {
$sqlSearch .= "surname LIKE '" . $surnameS . "%' and ";
In order to execute this statement the user must select all the options; the statement will not work if the user selects one or two options.
Upvotes: 0
Views: 90
Reputation: 42752
Start out with a placeholder like 1=1
which will always be true, and then use AND
as a prefix instead of a suffix.
$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";
if ($dateS != "") {
$sqlSearch .= " AND add_date = '" . changeDateSlashToHypen($dateS) . "'";
}
...
But as pointed out in the other answer you need to use prepared statements. So, assuming you're using mysqli, which everyone seems to do for some reason:
$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";
$types = "";
$parameters = [];
if ($dateS != "") {
$sqlSearch .= " AND add_date = ?";
$types .= "s";
$parameters[] = changeDateSlashToHypen($dateS);
}
if ($cdcS != "") {
$sqlSearch .= " AND cdc = ?";
$types .= "s";
$parameters[] = $cdcS;
}
if ($ppS != "") {
$sqlSearch .= " AND passport LIKE ?";
$types .= "s";
$parameters[] = "%$ppS%";
}
if ($surnameS != "") {
$sqlSearch .= " AND surname LIKE ?";
$types .= "s";
$parameters[] = "$surnameS%";
}
$stmt = $db->prepare($sqlSearch);
if (count($parameters) {
$stmt->bind_param($types, ...$parameters);
}
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
...
}
Upvotes: 1
Reputation: 204934
Don't patch your query together like this. Use Prepared Statements. Example:
SELECT *
FROM seafarers
WHERE (:dt is null or add_date = :dt)
and (:cdc is null or cdc = :cdc)
You have to fill the parameters of the query before execution.
Upvotes: 2