I'm making a song database system, and I'm trying to implement a way for users to search songs based on a category to search (song's name, artist's name, album name, genre etc) and a given search term. To accommodate user input and protect against SQL injection, I'm using a prepared statement made with bind variables, however I'm having trouble with what I currently made:
search("genre", "electropop", $db);
function search($column, $term, $db) {
try {
$sql = "SELECT * FROM Songs WHERE :column=:term;";
$stmt = $db->prepare($sql);
$params = array("column" => $column,
"term" => $term);
$stmt->execute($params);
$arr = $stmt->fetchALL(PDO::FETCH_ASSOC);
print (json_encode($arr));
}
catch(PDOException $ex) {
catch_error("The search failed.", $ex);
}
}
Whenever I test this, I get an empty array back: [ ] I tested my query ("SELECT * FROM Songs WHERE genre='electropop'") in phpmyadmin and it checks out (gave me back entries). The correct syntax for WHERE clauses in SQL is that the term needs to be surrounded by quotations (https://www.w3schools.com/sql/sql_where.asp) so I tried escaping quotation marks around the term:
...
$sql = "SELECT * FROM Songs WHERE :column=\':term;\'";
...
But then it fails to even see :term as a token to bind variables to later.
Not sure how to go about solving this. I assumed the empty array is due to a valid search but just no results, but perhaps I'm not correctly using a prepared statement. Any help would be much appreciated! Thanks!
You missed the : before term param. Don't need to bind column name. Just use the $column var instead of :column.
search("genre", "electropop", $db);
function search($column, $term, $db) {
try {
$sql = "SELECT * FROM Songs WHERE $column=:term;";
$stmt = $db->prepare($sql);
$params = array(":term" => $term);
$stmt->execute($params);
$arr = $stmt->fetchALL(PDO::FETCH_ASSOC);
print (json_encode($arr));
}
catch(PDOException $ex) {
catch_error("The search failed.", $ex);
}
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments