CODEWITHSUNDEEP
sqlsecuritydatabase-security
Matt Fenwick
Matt Fenwick

Reputation: 49085

Escaping/sanitizing data retrieved from the database

Let's say I have a web application that gets input from the user and saves it in a database. Let's further assume that there are no security vulnerabilities -- it correctly escapes user input, uses bind parameters, whatever.

Must data retrieved from the database be treated with suspicion (i.e. as potentially tainted/malicious)?

Example (not sure of the result because I'm afraid to try it). This is the database:

create table mytable (id int primary key, name varchar(50));

create table othertable (name varchar(50), xyz int, 
    ... `name` is an fk ...);

insert into mytable (id, name) values(1, '"abc"; drop table mytable;');

insert into othertable (name, xyz) values('"abc"; drop table mytable;', 45475);

Then I run this pseudo-code (maybe from PHP, for example):

# run query 'select * from mytable where id = 1';

# put the `name` in $name

# run query 'select * from othertable where name = $name'
# $name is not escaped, no other precautions taken

Upvotes: 1

Views: 105

Answers (2)

RonLugge
RonLugge

Reputation: 5174

You MUST escape again. All escaping does is say 'SQL, this isn't a command oriented ', it's a part of the data'. So if you escape " 'one' ", SQL will store " \'one\' ", and output... " 'one' ". Which means that you have to escape all over again.

Better yet, instead of using the regular mysql_ functions, use prepared statements from either mysqli_ or PDO. I'm moving my own programming approaches over because they obviate the need for escaping. (The basic idea is that instead of sending a query string which has to be parsed with the data 'in place', you send over a query string with placeholders, and then tell SQL 'remember that query I gave you earlier? Use values X, Y, and Z in it. As a result, the values never have a chance to corrupt the processing of the actual query)

Upvotes: 1

Raymond Hettinger
Raymond Hettinger

Reputation: 226256

The vulnerability happens at the point where you substitute the $name. Always escape before substituting.

Upvotes: 0

Related Questions