CODEWITHSUNDEEP
phpmysqlpdo
Pieter Dijkstra
Pieter Dijkstra

Reputation: 121

Escape a pdo query, is that necessary?

My question of to day is. Do i need to escape PDO in my script?

$columns = implode(", ",$column);
$query = ''.$query.' '.$columns.' FROM '.$table.'';
$dbh_query = $dbh->prepare($query);
$dbh_query->execute();
$dbh_querys = $dbh_query->fetchAll();

return $dbh_querys;

The whole script can be found at. https://github.com/joshuahiwat/crud/blob/master/control/query_connector.class.php

Can someone explain why do i need a escape at this time or why not.

I like to hear from you, thanks a lot!

Upvotes: 3

Views: 260

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562270

The parts of your query that are dynamic are the table name and column names. You can't use bind functions for these parts of the query. Bind functions can be used only for the parts of the query that would otherwise be a simple value in an SQL query. Like a numeric constant, or a quoted string or quoted date literal.

To avoid SQL injection from dynamic table names or column names, you have the following choices:

  • Use values that are predefined in your class, or otherwise certain to be safe. Don't use external content from users or any other source.
  • Use escaping. Note that the function PDO::quote() doesn't do the kind of escaping you need for table names or column names.
  • Create a "allowlist" of known table names and the column names for the respective table, and compare the dynamic input to the allowlist. If it doesn't match the allowlist, raise an error.

Upvotes: 3

Your Common Sense
Your Common Sense

Reputation: 157839

First of all you need to understand that the word you are using - "escape" - is meaningless.

What you probably mean is "to make your query safe from SQL injection". But, unfortunately, there is no such magic "escaping" that will make some abstract query safe.

The traditional query building assumes that all the query parts beside data values are hard-coded, while data values are bound via placeholders, like this:

$query = 'SELECT col1, col2 FROM some_table WHERE id = ?';
$stmt = $dbh->prepare($query);
$stmt->execute([$id]);
$row = $stmt->fetch();

This kind of a query considered safe.

In your case of a dynamically constructed query, every part is potentially vulnerable.

And here it is very important to understand that a burden of sanitizing all the query parts is entirely on this function. You cannot dismiss the danger simply claiming that your data is coming from the trusted source. That's a slippery ground because people often have no idea whether their source is trusted or not.

So, if take your question as "Do I have to protect this code from SQL injection", than the answer is - YES, YOU HAVE.

In the meantime you are protecting only a small part of your query - the data values. So you still have to protect (this term is much better than "escape") all other parts.

On a side note, your code is connecting to database every time it runs a query, which is highly inefficient and makes it impossible to use some database features.

Upvotes: 2

Related Questions