Safe input using parameterized binding and escaping / other methods?

[greenace92]

This is always bothering me as I'm not sure if I am using safe/secure methods of accepting input.

 

All of my php-inserts are parameterized but I am wondering if I still should escape / use filter function.

 

Can someone clear this up?

 

I've looked at a few sites and a couple posts have said "Prepared statements only way to guarantee against SQL Injection." So if I use prepared statements, is that it then, am I okay? Or should I still perform the escaping/filtering?

 

Thanks for any help

 

http://stackoverflow.com/questions/2009910/basic-mysql-php-filtering

http://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php

http://php.net/manual/de/ref.filter.php

http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php

 

[Jacques1]

Hi,

 

prepared statements are the primary mechanism for making sure that input data cannot change the structure of a query. If used correctly, they reliably prevent SQL injection attacks as well as accidental syntax conflicts (like when you try to insert a single quote into a single-quoted string literal).Note that some database interfaces like PDO use “fake” prepared statements which do not provide this level of robustness. By default, calling PDO::prepare() and PDOStatement::execute() merely auto-escapes the input, inserts it into the query string and then sends the whole string to the database system. This is much less secure and has the exact same issues as manual escaping (see below). An actual prepared statement always consists of two separate steps: You send a query template to the database system, and then you pass specific data to this template and execute it. To enable this two-step process in PDO, you need to explicitly turn PDO::ATTR_EMULATE_PREPARES off.

 

As a secondary line of defense, you should validate the input data whenever possible. For example, use ctype_digit() to make sure that a variable only contains decimal digits. If it doesn't, reject the input and generate an appropriate error message.

 

Do not try to “fix” the input with the FILTER_SANITIZE_* constants or through type casting. This is extremely confusing and can cause severe problems. For example, let's say I ask you to delete the row with the ID “12a3”, which is clearly an incorrect request. It would be insane if you picked a different ID (like “12”) and deleted that row instead, because I never asked you to. Type casting can also lead to truncation bugs.

 

Escaping is a less secure alternative to prepared statements. They cannot be used together, because all the escape characters would literally be inserted into your database. The input doesn't run through any SQL parser (that's the whole point of prepared statements).

 

There are many problems associated with escaping, so it should generally we avoided:

• Even the best developers forget to escape a value from time to time, or maybe they think it's safe when it isn't. This immediately leads to a potential SQL injection vulnerability.
• Escaping isn't trivial. There are dozens of incorrect functions, and even if you've picked the right one, you may still screw up (e. g. mysql_real_escape_string() is useless without surrounding quotes).
• Since escaping happens in the application rather than in the database system itself, there's always a certain risk that a seemingly safe query is misinterpreted and turned into an SQL injection by the database system.

The last problem is particularly nasty, because it can happen even when you think you've done everything correctly. For example: If the application uses backslash-escaping, it will produce queries like

SELECT username FROM users WHERE id = '123\'UNION SELECT password FROM users-- '

From the application's perspective, this is perfectly safe, because it has the following structure:

SELECT username FROM users WHERE id = <some string>

However, the database system may have disabled backslash-escaping, so it interprets the query like this:

SELECT username FROM users WHERE id = <some string> UNION SELECT password FROM users

That's a full-blown SQL injection which yields all password hashes stored in the database. The same can happen if the application doesn't use the same character encoding as the database.

 

If you do need to use escaping (e. g. on legacy systems), you have to be super-careful:

• Escape all values, even if you think they're safe.
• Only use mysql_real_escape_string(). Forget about addslashes() or anything like that.
• The escaped string must be enclosed in quotes. Otherwise escaping is useless.
• If you need to change the character encoding at runtime, use mysql_set_charset(). Do not run a SET NAMES query, because this can lead to an encoding mismatch between the database system and the application.

Long story short: Prepared statements are by far the most robust solution as long as you make sure they're not “emulated” in the application.

Edited September 26, 2015 by Jacques1

[greenace92]

Oh man, Thank you very much Jacques1, bookmarked!

 

Going to implement these standards you mentioned for future projects.

 

How would I know if an input is not emulated?

 

I see this

 

 

PDO::ATTR_EMULATE_PREPARES off.

 

Will have to re-read thoroughly

Edited October 15, 2015 by greenace92

[Jacques1]

MySQLi doesn't use emulation at all, PDO must be configured with PDO::ATTR_EMULATE_PREPARES set to false:

$databaseConnection = new PDO($dSN, DB_USER, DB_PASSWORD, [
    PDO::ATTR_EMULATE_PREPARES => false,    // disable emulation so that actual prepared statements are used
    ...
]);

A quick test is to prepare an invalid query. If emulation is used, nothing will happen, because the query template doesn't get processed at that point. If an actual prepared statement is used, then you'll get an error, because the query template is sent to the database system where it cannot be processed:

<?php

const DB_HOST = 'localhost';
const DB_USER = '...';
const DB_PASSWORD = '...';
const DB_NAME = '...';
const DB_CHARSET = 'UTF8';



$dSN = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET;
$databaseConnection = new PDO($dSN, DB_USER, DB_PASSWORD, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,    // activate exceptions
]);

// preparing an invalid query *with* emulation: nothing happens
$databaseConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$databaseConnection->prepare('THIS IS NO VALID SQL');

// preparing an invalid query *without* emulation: an exception is triggered
$databaseConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$databaseConnection->prepare('THIS IS NO VALID SQL');

[greenace92]

Is it bad if I don't know how to use PDO? I am only really familiar with MySQLi, and I haven't used it much aside from inserting/querying to display rows.

[Jacques1]

PDO is actually much easier to learn and use than MySQLi, so I'd give it a shot.

 

If you still prefer MySQLi, that's fine. But be aware that it can become very tedious (e. g. fetching data from a prepared statement), and it creates a kind of “vendor lock-in”, because you won't be able to switch to a different database system.

[greenace92]

No I agree, I will opt to learn PDO. I'm pretty early in the game and haven't done too much so it's good to switch to the more common practice.

 

I like the idea of switching databases I have read that Google for example is leaving SQL if I'm not mistaken.

 

Thanks for the great points.

[Jacques1]

PDO is not a database abstraction layer, so you won't be able to simply plug in some NoSQL database. However, you can switch to a different SQL database system like PostgreSQL as long as you have the driver and rewrite the queries which are MySQL-specific.

[greenace92]

Oh man, I guess I have to really know what I aim to make in the long run as far choosing server os and then database. But I'll keep that in mind. There is so much to learn, such a responsibility.