Variable in SQL select

Question

I'm trying to make a search bar with PHP to show the product that we got in store with a get script. Now I want to put the variable I get in my SQL query like this:

$search = $_GET['q'];

$sql = "SELECT
`product`.`productcode`,
`product`.`productnaam`,
`product`.`prijs`,
`product`.`voorraad`,
`afbeelding`.`image_id`,
`afbeelding`.`image_ctgy`
FROM `product`, `afbeelding`
WHERE `product`.`productcode` = `afbeelding`.`image_id` AND `afbeelding`.`image_ctgy` = $search
GROUP BY `productnaam`
ORDER BY `productnaam`;";

How do I make it so the variable doesn't mess with the query?

You mean how to secure your query from the outer $_GET variable? — Loai Abdelhalim, Jan 18 '16 at 18:07
single quotes.. but SQL injection is a problem. Look into PDO. — CodeGodie, Jan 18 '16 at 18:10
https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Jan 18 '16 at 18:57

score 3 · Answer 1 · edited Jan 20 '16 at 18:53

Take a look at PDO prepared statements.

They allow you to use a variable inside your query without worrying about MySQL injections too much.

$stmt = $dbh->prepare("SELECT
    `product`.`productcode`,
    `product`.`productnaam`,
    `product`.`prijs`,
    `product`.`voorraad`,
    `afbeelding`.`image_id`,
    `afbeelding`.`image_ctgy`
    FROM `product`, `afbeelding`
    WHERE `product`.`productcode` = `afbeelding`.`image_id` AND `afbeelding`.`image_ctgy` = :search
    GROUP BY `productnaam`
    ORDER BY `productnaam`");

$stmt->bindParam(':search', $search);

schellingerht · Answer 2 · 2016-01-18T18:23:45.927

$search = mysqli_real_escape_string($_GET['q']);

$sql = "SELECT product.productcode, product.productnaam,  product.prijs, product.voorraad, afbeelding.image_id, afbeelding.image_ctgy FROM    product, afbeelding WHERE product.productcode = afbeelding.image_id AND afbeelding.image_ctgy = '" . $search . "' GROUP BY productnaam ORDER BY productnaam";

Or use PDO instead (OOP):

$dbh = new mysqli($servername, $username, $password, $dbname);

$stmt = $dbh->prepare("SELECT product.productcode, product.productnaam,  product.prijs, product.voorraad, afbeelding.image_id, afbeelding.image_ctgy FROM    product, afbeelding WHERE product.productcode = afbeelding.image_id AND afbeelding.image_ctgy = :search GROUP BY productnaam ORDER BY productnaam");

if ($stmt->execute([':search' => $_GET['q']])) {
    while ($row = $stmt->fetch()) {
        print_r($row);
    }
}

If you're using an older version of php, replace [':search' => $_GET['q']] with array(':search' => $_GET['q'])

score 0 · Answer 3 · edited Jan 20 '16 at 18:54

0

Use filter_input(). It will make sure the send $_GET variable is a secure string.

In your example:

// q is the name of the $_GET variable.
$search = filter_input(INPUT_GET, 'q', FILTER_SANITIZE_SPECIAL_CHARS);

edited Jan 20 '16 at 18:54

Peter Mortensen

30,738
21
105
131

answered Jan 18 '16 at 18:15

Loai Abdelhalim

1,901
4
24
28

Variable in SQL select

3 Answers3