Sql, change WHERE statement to any variable

Question

From the database to display whatever is searched or searched like,

 $sql = "SELECT * FROM songs WHERE **VARIABLE** LIKE '%SEARCH%'";

Where it says "VARIABLE" I want to use a variable to change it every time there is a different search location, like the genre then artist.

So for "VARIABLE", it's could be replaced with

$searchLocation

Whereas $searchLocation is equal to genre or artist depending on what you choose from the previous page

<?php       
$searchLocation=$_GET['searchLocation'];
$searchValue=$_GET['searchValue'];

include 'database.php';
$pdo = Database::connect();
$sql = "SELECT * FROM songs WHERE '%$searchLocation%' LIKE '%$searchValue%'";
foreach ($pdo->query($sql) as $row) {

};
Database::disconnect();
?>

Is there a question here? Have you spent an time searching Stack Overflow before you posted this? — Tim Biegeleisen, Apr 23 '18 at 15:56
You dont need the `%` at the start and end of `'%$searchLocation%'` and the single quotes are also syntactically incorrect so try `$searchLocation` But that does leave you wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Apr 23 '18 at 15:57
Please do not do it that way. It opens a door for SQL Injection. Column names/table names should not be a variable. — GrumpyCrouton, Apr 23 '18 at 15:58
@GrumpyCrouton Sorry had to look for my SQL Injection warning — RiggsFolly, Apr 23 '18 at 15:59

score 0 · Answer 1 · answered Apr 23 '18 at 16:04

Talking about the dynamic column and not the dynamic value for a moment...

Schema object identifiers (columns, tables, etc.) can't be parameters. So sometimes in cases like this you might have to step outside of the "always use parameters" mantra and dynamically build your query from variables. For example:

$sql = "SELECT * FROM songs WHERE $columnName LIKE '%SEARCH%'";

Then the million dollar question to assuage our fears of SQL injection is:

Where does $columnName come from?

It should never come from user input. Fortunately, column names are finite and known ahead of time. So you can give the user the ability to specify one, but you have data you can validate that input against. Consider a whitelist approach such as:

User specifies a column name.
You compare against a known list of columns (either hard-coded or pulled form the database schema).
If there is no match, return an error.
If there is a match, use the matched element from the known list.

This allows you to dynamically build your SQL, but not by using anything which was ever user-editable. Santizing user input is one thing, but not having to rely on it in the first place and always using known good values is something else entirely. After all, someone may balk at this:

$sql = "SELECT * FROM songs WHERE $columnName LIKE '%SEARCH%'";

But it's hardly SQL-injectable in this context:

$columnName = "title";
$sql = "SELECT * FROM songs WHERE $columnName LIKE '%SEARCH%'";

Which is no different from:

$sql = "SELECT * FROM songs WHERE title LIKE '%SEARCH%'";

The value being searched ('%SEARCH%' in this case, which would change to use whatever your database API uses for adding parameters to a query) should certainly be a parameter and not concatenated directly into the query. But schema object identifiers are a different story.

Sql, change WHERE statement to any variable

1 Answers1