Replace content with get parameter for a SQL query

Question

Given this query

$query = "SELECT * FROM products WHERE category = {category} AND price = '{price}'"

with these $_GET params: category = 10 and price = $60

I want to replace the content inside the {} with the actual GET values to get a sql query like this one:

"SELECT * FROM products WHERE category = 10 AND price = '$60'"

but if one or more or the parameters are missing i want to replace the content of the {} with something different to avoid the execution. for example:

"SELECT * FROM products WHERE category = category AND price = price"

note: I know about sql injection. that is a different subject. what i want to know if how to avoid the execution of a sql stamente if the value (from get) is not present. for example select * from products where id = id returns all the products but select * from products where id = 100 returns only one (with id 100)

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

in regards to the comment I would suggest you change your query to something like this.

//variables
if(isset($_GET['category']) && isset($_GET['price'])) {
    $category = $_GET['category'];
    $price = $_GET['price'];

    //create server instance
    $mysqli = new mysqli("server", "username", "password", "database_name");

    //make sure there was no errors with the connection
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    //create a parameterized query
    $query = $mysqli->prepare("SELECT * FROM products WHERE category = ? AND price = '?'");

    //bind query params
    $query->bind_param("s", $category, $price);

    //execute the query and then clean up
    $query->execute();
    $query->close();
    $mysqli->close();
}

See my POST here for more details

edited May 23 '17 at 11:49

Community

1
1

answered Sep 09 '14 at 14:37

John Ruddell

25,283
6
57
86

what if the category param is not present? that part of the query will be executed by SQL OR ignored? – handsome Sep 09 '14 at 14:50
@handsome I wasn't trying to write the whole thing for the OP but just give the necessary components.. but I included that in the answer as well. its in the question that i linked to where i gave an answer. – John Ruddell Sep 09 '14 at 14:55
i know but that is exactly what i want to fix. if the value if not present then replace the {} with the same so SQL will not execute that. just like doing SELECT * FROM table WHERE 1 = 1 – handsome Sep 09 '14 at 15:03
@handsome oh ok.. well you need to use isset() if that if statement fails then the parameters werent sent and it wont do the sql query.. see the edit I did – John Ruddell Sep 09 '14 at 15:04
Sorry i think im not be clear enough. i want to something like a fallback. based on your example something like this: $query->bind_param("id", $id, 'id'); – handsome Sep 10 '14 at 01:25

score 0 · Answer 2 · answered Sep 09 '14 at 14:37

0

For one simple query this is way OP, but take a look at Doctrine, it's a perfect layer to handle your queries

answered Sep 09 '14 at 14:37

Pieter Verloop

115
6

score 0 · Answer 3 · answered Sep 09 '14 at 14:38

This not the right way to tackle this problem because of the SQL Injection. Try this:

if(isset($_GET['category']) && isset($_GET['price'])) {
    // you might want to consider validating user input
    //use either PDO or MySQLi with parametized Query e.g.
    $stmt = $pdo_instance->prepare("SELECT * FROM products WHERE category = :category AND price = :price");
    $stmt->bindValue(":category", $_GET['category']);
    $stmt->bindValue(":price", $_GET['category']);
} else if(isset($_GET['category'])) {
    //use either PDO or MySQLi with parametized Query e.g.
    $stmt = $pdo_instance->prepare("SELECT * FROM products WHERE category = :category");
    $stmt->bindValue(":category", $_GET['category']);
} else if(isset($_GET['price'])) {
    //use either PDO or MySQLi with parametized Query e.g.
    $stmt = $pdo_instance->prepare("SELECT * FROM products WHERE price = :price");
    $stmt->bindValue(":price", $_GET['price']);
}

thanks but i cant rewrite the query. i know about SQL Injection. i just wanted to give you guys an example without distraction — handsome, Sep 09 '14 at 15:07

Replace content with get parameter for a SQL query

3 Answers3