How can I escape URL parameter for using it into the query?

Question

I have a function which generates a query. Something like this:

// this is a simplified version of my real code
public function get_query(){
    $name = $_GET['name'];
    return "SELECT * FROM mytable WHERE name = $name";
}

As you know, such queries aren't safe. Because they are threatened by SQL injection. Anyway, I need to escape $name variable before using it into the query.

Well there are two approaches: (since I use PDO. Otherwise there is also an old way which is using mysql_escape_string())

Using PDO::prepare()
Using PDO::quote()

Both of them need the PDO connection which isn't access into the generator query function. Now I want to know, is there any other approach?

@scaisEdge I don't want to pass parameter like `?` or`:param` in the query. I just want to make a query as string. — Martin AJ, Jun 11 '17 at 19:57
Duplicate: https://stackoverflow.com/questions/1162491/alternative-to-mysql-real-escape-string-without-connecting-to-db — Sky, Jun 11 '17 at 20:02
it is impossible to safely escape a string without a DB connection. — Sky, Jun 11 '17 at 20:03

FirstOne · Answer 1 · 2017-06-18T12:44:12.497

Yes. You could have the function return something that will later be used with PDO. Since you said it's not available in generation time, do any step needed beforehand, then use PDO, because well, you'll have to, some time.

Where PDO is not available:

public function get_query(){
    $name = $_GET['name'];
    return array(
        'query' => 'SELECT * FROM mytable WHERE name = :name',
        'params' => array(':name', $name)
    );
}

Where PDO is available:

// pass '$values' to a class where a connection is available
$values = get_query();
$pdo->prepare($values['query']); // $pdo->prepare('SELECT * FROM mytable WHERE name = :name');
$pdo->execute($values['params']); // $pdo->execute(array(':name', value_of_$name));

It would look like the Second Example from the Manual. It will allow you to still use prepared statements with parameterized queries, even without knowing what query / parameters are being used.

Some notes:

Escaping is not enough to prevent sql injection;
This won't change much of your original idea, since you'd still pass to PDO the generated query returned from get_query();
In case your query generation is too complex, you could just use ? in the query as unnamed parameters - similar to example 3 from the manual - provided the values are passed in the correct order.

How can I escape URL parameter for using it into the query?

1 Answers1