Sql injection attacks

Geoff Caplan wrote:

I'm new to Postgres and trying to get up to speed on the security
issues. There seems to be remarkably little Postgres specific stuff
on preventing SQL injection attacks.

If you use prepared statements (the details of which vary by programming
language), you should be quite safe.

Most of the online literature is on MS SQL Server. There, the
consensus seems to be that the range of potential attacks is so wide
that attempting to spot attack signatures in posted data is a doomed
enterprise, and that the safest general approach for any dynamically
built query is to execute it as a stored procedure.

That won't necessarily help you on PostgreSQL, because in stored
procedures you can paste together queries from user-provided strings.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Geoff Caplan <geoff@variosoft.com> wrote:

Hi folks,

I'm new to Postgres and trying to get up to speed on the security
issues. There seems to be remarkably little Postgres specific stuff on
preventing SQL injection attacks.

Most of the online literature is on MS SQL Server. There, the
consensus seems to be that the range of potential attacks is so wide
that attempting to spot attack signatures in posted data is a doomed
enterprise, and that the safest general approach for any dynamically
built query is to execute it as a stored procedure.

Huh?

To protect yourself from SQL injections, just pass all your data through
PQescapeString() (or the appropriate function for the language you're
developing in). That will escape any character that could be used to
initiate an SQL injection, thus foiling the attempt.

That "consensus" you speak of doesn't make much sense to me. Besides,
who cares about "detecting" the SQL injection, as long as it's unable
to work.

In SQL Server, this reportedly works because the syntax of the query
is pre-compiled, and the variables passed in are treated strictly as
data and cannot alter the syntax. So any malicious use of "AND",
"UNION", ";" etc in submitted data will fail.

Can anyone confirm that this would also apply to Postgres Query
Language (SQL) functions? The effectiveness of moving the queries into
the database against SQL injection attack would seem to depend on the
query engine internals. Will using the SQL functions provide the
robust protection I am looking for?

No, and I can't see how it will protect in MS SQL server either? They'll
just learn that they have to escape out of the command that calls the
stored function.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Hi folks,

Peter Eisentraut wrote:

PE> If you use prepared statements (the details of which vary by >>
PE> programming language), you should be quite safe.

Peter - thanks for the suggestion. You are right: a poorly designed
function might simply concatenate the injected code - I hadn't really
thought it through. The key seems to be to treat the unsafe string as
a value so it can't leak out into the statement, and a parameterised
prepared statement would do this effectively, as you suggest. Very
elegant...

Bill Moran wrote:

BM> To protect yourself from SQL injections, just pass all your data through
BM> PQescapeString()

I'm no expert, but the papers I have been reading suggest that the
usual hygene advice such as don't display DB error messages and escape
unsafe strings doesn't cover all types of attack. See, for example,
this:

http://www.net-security.org/article.php?id=571

But so far as I can see, Peter's suggestion should provide a workable
robust solution. So thanks again!

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154

Geoff Caplan <geoff@variosoft.com> wrote:

BM> To protect yourself from SQL injections, just pass all your data through
BM> PQescapeString()

I'm no expert, but the papers I have been reading suggest that the
usual hygene advice such as don't display DB error messages and escape
unsafe strings doesn't cover all types of attack. See, for example,
this:

http://www.net-security.org/article.php?id=571

But so far as I can see, Peter's suggestion should provide a workable
robust solution. So thanks again!

Hope that works for you. I still think you're ignoring basic data validation.

Simply put:
1) If the untrusted value is a string, using a proper escape sequence should
make it safe.
2) If the untrusted value is not a string, then it should be tested for
proper value (i.e. if it should be a number, it should be ensured that
it _is_ a number, and nothing else) invalid values should trigger an
error.

I don't see how storing the SQL in some different location is the correct
way to fix anything? Besides, the suggestions made in that paper only work
with ASP and JDBC ... how do you accomplish the same thing in PHP, for
example?

Just my opinion. Take it or leave it as you see fit.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Bill Moran <wmoran@potentialtech.com> wrote:

[snip]

Simply put:
1) If the untrusted value is a string, using a proper escape sequence should
make it safe.
2) If the untrusted value is not a string, then it should be tested for
proper value (i.e. if it should be a number, it should be ensured that
it _is_ a number, and nothing else) invalid values should trigger an
error.

I don't see how storing the SQL in some different location is the correct
way to fix anything?

[snip]

I agree with Bill. Years ago (more years than I care to recall) I read
a book on structured systems design (IIRC) that advised one should
condition/convert data as early as possible in the process, throughout
the design. Amongst the advantages cited for this tactic was that then
you would know, everywhere else in the system, that you were dealing
only with conditioned data. That practice, taken to heart relatively
early in my career, has always stood me in good stead. Thus I
recommend to others the same approach.

In short: Any data coming from an untrusted source should always be
de-fanged as early as possible.

Jim

Bill Moran <wmoran@potentialtech.com> writes:

Geoff Caplan <geoff@variosoft.com> wrote:

Hi folks,

I'm new to Postgres and trying to get up to speed on the security
issues. There seems to be remarkably little Postgres specific stuff on
preventing SQL injection attacks.

Most of the online literature is on MS SQL Server. There, the
consensus seems to be that the range of potential attacks is so wide
that attempting to spot attack signatures in posted data is a doomed
enterprise, and that the safest general approach for any dynamically
built query is to execute it as a stored procedure.

Huh?

To protect yourself from SQL injections, just pass all your data through
PQescapeString()

Or better yet don't mix your data with your code.

Any the literature that suggests interpolating your data into your SQL queries
using some escaping mechanism is in my humble opinion, leading you down the
garden path. It's the wrong way to think about things.

You should never ever write code that mixes data with executable code. Doing
so is just asking for trouble. Even if you know about PQEscapeString, it's
hard to verify that PQEscapeString has been called in every single place where
it's needed. One day you'll miss one place and all that effort becomes futile.

Better to just never mix the two. Let the driver handle marshalling the data
and transporting it to the database. All good driver APIs have an interface
that allows you to ship the data as separate parameters.

Something like (in Perl)

$sth = $dbh->prepare('select * from foo where a=?');
$sth->execute($dangerous_data);

or (in PHP)

$row = $db->getone('select * from foo where a=?', array($dangerous_data));

If you get into the habit of doing things this way normally, never just
interpolating variables into your sql code, then the rare instance when you do
have to interpolate something will stand out like a sore thumb. And it should
be easy to see whether that something is safely coming from static data in the
code or is dangerously coming from outside data from the network.

Note that not all database engines and drivers will actually be able to avoid
interpolating the data into the SQL eventually. Postgres as of 7.4 is capable
of it but even for Postgres not all the drivers have been updated. But that's
mostly irrelevant, at least make it the responsibility of the driver to do the
interpolating, it's more likely to get it right and whatever it does, it will
at least be 100% consistent about it.

But mixing the data with the code, even if you use PQEscapeString is just a
bad programming practice.

--
greg

Bill Moran wrote:

Simply put:
1) If the untrusted value is a string, using a proper escape sequence

should

make it safe.

in pgsql (and mysql) you can escape almost everything.

update table set a = '5' is corrent, even is column a is integer type.
You can't escape the null value.

Mage

Most of the online literature is on MS SQL Server. There, the
consensus seems to be that the range of potential attacks is
so wide that attempting to spot attack signatures in posted
data is a doomed enterprise, and that the safest general
approach for any dynamically built query is to execute it as
a stored procedure.

This recommendation is one of the main reasons there are *soooo* many
SQL-injection vulnerable sites built on the ASP/SQLServer combination by
people that *think* they are safe. It used to be in the MS
documentation, but it's supposed to be all gone now. But it's still in a
lot of other guides out there.

Using a stored procedure is just as unsafe as using a direct SQL
statement, *unless* you use the parametrised (eh, is it really called
that? Wel..) format. And the other way around, using direct SQL is just
as safe *iff* you use parametetrised format.

"SELECT * FROM foo WHERE bar=" & request("bar")
is no more dangerous than
"sp_foo " & request("bar")

If you use the parametrised formats, e.g.
"SELECT * FROM foo WHERE bar=@bar"
or just "sp_foo" in the case of the stored procedure (the driver will
pick up parameter names automatically)

Then it's the responsibility of the driver to handle any escaping
needed. And AFAIK it does a good job of it.

Similar methods are of course available in pretty much all other DB
interfaces, depending on language/framework.

In SQL Server, this reportedly works because the syntax of
the query is pre-compiled, and the variables passed in are
treated strictly as data and cannot alter the syntax. So any
malicious use of "AND", "UNION", ";" etc in submitted data will fail.

Yes, this will fail. However, injecting something like:
"sp_foo 'bar' ; DROP TABLE foo"
is still very possible.

Can anyone confirm that this would also apply to Postgres
Query Language (SQL) functions? The effectiveness of moving
the queries into the database against SQL injection attack
would seem to depend on the query engine internals. Will
using the SQL functions provide the robust protection I am
looking for?

No. Parametrised queries will.

There are other reasons for moving the querys to the backend (isolation
and performance depending on your situation), but it won't provide the
protection you're asking for here.

//Magnus

Hi folks

Seems we have two schools of thought:

1) The validation/escaping approach, supported by Bill and Jim

2) The "don't mix data with code" approach supported by Peter and
Greg.

As I learn more about the issues, I am increasingly veering towards
the second approach.

Obviously, proper validation is a given for all kinds of reasons. But
the problem with validation/escaping as the primary defense against
injection seems to be that simply escaping would not catch every type
of insertion via strings.

If you are just saving the untrusted string as a value, then the
attacker needs to use a quote or comment to escape out of the value
and alter the query syntax. This is relatively easy to counter, as
Bill suggests.

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

In that scenario, if you just concatenate your untrusted data into
your query, it is relatively easy for the attacker to sneak in
AND/OR/UNION type clauses without using quotes. This means that you
have to check strings for attack signatures as well, which is not
trivial if you are going to avoid false positives. And it means that
every single untrusted string has to be screened by someone skilled at
analysing the potential threats and writing the right Regexes. And as
Greg point out, you only have to screw up once and you have opened up
your whole system. Plus, as new attacks are developed, you have to
audit all your code to counter them. This surely can't be the way to
go.

Bill wrote:

BM> I don't see how storing the SQL in some different location is the
BM> correct way to fix anything?

That was my original suggestion, and I agree it was naive. But I don't
think that is what Peter is suggesting. I think the idea is that
parameterised prepared statements compile the SQL statement before the
user input is added via the parameters, making it impossible for user
input to modify the actual SQL statement.

From the practical point of view, it surely seems easier to adopt a
particular style of programming ("any query using untrusted data MUST
be a parameterised prepared statement") than to analyse each and every
untrusted parameter for attack signatures? Or am I missing something
here?

------------------
Geoff Caplan
Vario Software
(+44) 121-515 1154

Magnus,

Your posting arrived just after I posted my attempt at a summary...

With the help of the list, I had already got to the stage that
parameterised queries are the way to go. Your post helps confirm
that.

Now I need to understand the implementation details. Clearly, one
option is the PREPARE/EXECUTE statements. But I am not sure I follow
you when you say:

MH> If you use the parametrised formats, e.g.
MH> "SELECT * FROM foo WHERE bar=@bar"
MH> or just "sp_foo" in the case of the stored procedure (the driver will
MH> pick up parameter names automatically)

Are you saying that "@bar" is part of the Postgres query syntax? If it
is, I can't find it in the 7.4 docs. What do you mean by "the
parameterised formats"? I would appreciate a clarification and the URL
of the relevant documentation.

Thanks

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154

MH> If you use the parametrised formats, e.g.
MH> "SELECT * FROM foo WHERE bar=@bar"
MH> or just "sp_foo" in the case of the stored procedure (the driver
MH> will pick up parameter names automatically)

Are you saying that "@bar" is part of the Postgres query
syntax? If it is, I can't find it in the 7.4 docs. What do
you mean by "the parameterised formats"? I would appreciate a
clarification and the URL of the relevant documentation.

Sorry, this is the ADO syntax used in ASP - I was referring to the
incorrect claims about MSSQL you had found. It definitly applies to
MSSQL on ASP, not 100% sure about pgsql on ASP.

Another common syntax is the "?" syntax, e.g. "SELECT * FROM foo EBRE
bar=?", that's used in for example DBI.

So it depends on what interface you are using to connect to the server.
If it's straight libpq, you should look at PQexecParams() or
PQexecPrepared(), see
http://www.postgresql.org/docs/7.4/static/libpq-exec.html.

//Magnus

On Mon, Jul 26, 2004 at 08:08:35AM +0200 I heard the voice of
Mage, and lo! it spake thus:

Bill Moran wrote:

Simply put:
1) If the untrusted value is a string, using a proper escape
sequence should make it safe.

in pgsql (and mysql) you can escape almost everything.

update table set a = '5' is corrent, even is column a is integer type.
You can't escape the null value.

Which, IMO, is a great thing; I studiously trained myself to use the
escaping functions on every value I ever use in a query. If you
escape everything unconditionally, without worrying about what type
the column is, there's a lot less chance for mistakes and oversights.

--
Matthew Fuller (MF4839) | fullermd@over-yonder.net
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/

"The only reason I'm burning my candle at both ends, is because I
haven't figured out how to light the middle yet"

Geoff Caplan <geoff@variosoft.com> writes:

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

Can you give an example of this that isn't also an example of
obviously bad application design?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug,

DM> Geoff Caplan <geoff@variosoft.com> writes:

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

DM> Can you give an example of this that isn't also an example of
DM> obviously bad application design?

I'm no expert to put it mildly, but if you Google for "SQL Injection
Attack" you'll find a lot of papers by security agencies and
consultancies. You could start with these:

www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.net-security.org/article.php?id=142

They are SQL Server oriented, but many of the issues would apply to
Postgres.

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154

Geoff Caplan <geoff@variosoft.com> writes:

Doug,

DM> Geoff Caplan <geoff@variosoft.com> writes:

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

DM> Can you give an example of this that isn't also an example of
DM> obviously bad application design?

I'm no expert to put it mildly, but if you Google for "SQL Injection
Attack" you'll find a lot of papers by security agencies and
consultancies. You could start with these:

That doesn't answer my question. :)

If you're trusting the user (via GET or POST data) to hand you valid
SQL fragments, even just column names, you Deserve To Lose. The only
things that come in via GET or POST should be data values, and they
should either be explicitly escaped, or used in prepared statements
where the driver takes care of the escaping.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

At 09:58 AM 7/26/2004 +0100, Geoff Caplan wrote:

Seems we have two schools of thought:

1) The validation/escaping approach, supported by Bill and Jim

2) The "don't mix data with code" approach supported by Peter and
Greg.

As I learn more about the issues, I am increasingly veering towards
the second approach.

They are not really different schools of thought.

My suggestion:
0) Make it much easier to the right thing than the wrong thing. Prepared
statements help. You may still need to make it simpler for the programmers.
1) validate/filter/escape all input to your program so that your program
(NOT other programs[1]Do not combine filters, doing so often corrupts data. Boneheaded thinking like PHP's magic quotes create the silly multiple backslashed single quotes and similar stuff.) can deal with it.
2) validate/filter/escape output from your program to each destination
accordingly so that each destination can deal with it and treat it correctly.
3) Assume by default input could be from hostile sources, unless provable
otherwise.

[1]: Do not combine filters, doing so often corrupts data. Boneheaded thinking like PHP's magic quotes create the silly multiple backslashed single quotes and similar stuff.
thinking like PHP's magic quotes create the silly multiple backslashed
single quotes and similar stuff.

Also for PostgreSQL at least do not forget that _ and % are wildcards for
LIKE and similar queries and these wildcards are probably NOT escaped
whether explicitly by postgresql's builtin escaping functions or
automatically by the parameterizing methods. When I last checked you have
to escape them yourself if you want to.

Though insertion of wildcards is not exactly SQL injection they often cause
applications to behave differently from design. A wildcard in a LIKE query
could cause a full table scan, and thus:

1) Use up resources (CPU, memory, disk/network bandwidth). Possibly more
easily allow a Denial of Service attack.
2) Indicate to the attacker how many rows there are (e.g. how many
customers a particular CA had at a particular moment. Polling regularly
indicates how many customers added, or whether _your_ row was added
successfully (*grin*) ).

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

Don't do that. If you need to do that, you are doing things wrong. Very wrong.

Link.

Geoff Caplan <geoff@variosoft.com> writes:

Obviously, proper validation is a given for all kinds of reasons. But
the problem with validation/escaping as the primary defense against
injection seems to be that simply escaping would not catch every type
of insertion via strings.

I think you misunderstood. Escaping is perfectly safe (given a correct
escaping function) if it's used on *every* untrustworthy input string.
The argument for the "keep data separate from code" approach is
essentially just that it's easier to be sure you haven't forgotten
anyplace where you need to escape.

regards, tom lane

Hi folks

Doug wrote:

If you're trusting the user (via GET or POST data) to hand you valid
SQL fragments, even just column names, you Deserve To Lose. The only
things that come in via GET or POST should be data values, and they
should either be explicitly escaped, or used in prepared statements
where the driver takes care of the escaping.

I don't think we are disagreeing here - seems like sound advice to me.
I am just feeling my way into this database game, so perhaps I am not
expressing myself clearly...

Lincoln wrote:

They are not really different schools of thought.

My suggestion:

1) validate/filter/escape all input to your program so that your program
(NOT other programs) can deal with it.

That makes sense. What I am currently planning is to:

a) Restrict application urls to a small set of safe characters,
excluding spaces. The front-controller will run a safety check on the
url and abort the request if it looks odd. This should make it
impossible to inject into a GET variable. All cookies will carry a
digest with secret key so they can't be spoofed. That only leaves
posted and uploaded data to worry about.

b) Only write raw SQL for internal queries with static data. All other
queries will be either parameterised prepared queries, or constructed
via an API that will quote every value. The info from another poster
that it is OK to quote numbers makes this simple.

Also for PostgreSQL at least do not forget that _ and % are wildcards for
LIKE and similar queries and these wildcards are probably NOT escaped
whether explicitly by postgresql's builtin escaping functions or
automatically by the parameterizing methods. When I last checked you have
to escape them yourself if you want to.

Though insertion of wildcards is not exactly SQL injection they
often cause applications to behave differently from design.

Very useful: I wouldn't have thought of this...

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

Don't do that. If you need to do that, you are doing things wrong. Very wrong.

Please disregard that statement - I wasn't thinking clearly. What I
had in mind was that something like a product id in a url might find
its way into, say, a WHERE clause. What I overlooked is that it is
still, of course, being used as a value. My inexperience is showing,
I'm afraid!

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Doug McNaught wrote:<br>
<blockquote type="cite" cite="mid877jsq7seo.fsf@asmodeus.mcnaught.org">
<pre wrap="">Geoff Caplan <a class="moz-txt-link-rfc2396E" href="mailto:geoff@variosoft.com">&lt;geoff@variosoft.com&gt;</a> writes:

</pre>
<blockquote type="cite">
<pre wrap="">Doug,

DM&gt; Geoff Caplan <a class="moz-txt-link-rfc2396E" href="mailto:geoff@variosoft.com">&lt;geoff@variosoft.com&gt;</a> writes:

</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.
</pre>
</blockquote>
</blockquote>
<pre wrap="">DM&gt; Can you give an example of this that isn't also an example of
DM&gt; obviously bad application design?

I'm no expert to put it mildly, but if you Google for "SQL Injection
Attack" you'll find a lot of papers by security agencies and
consultancies. You could start with these:
</pre>
</blockquote>
<pre wrap=""><!---->
That doesn't answer my question. :)

If you're trusting the user (via GET or POST data) to hand you valid
SQL fragments, even just column names, you Deserve To Lose. The only
things that come in via GET or POST should be data values, and they
should either be explicitly escaped, or used in prepared statements
where the driver takes care of the escaping.

-Doug
</pre>
</blockquote>
Or, if your POST/GET data is necessary to alter your SQL statement,
then make it conditional, but never accept raw SQL from the user.&nbsp; It's
easy enough to check for certain fields (such as a checkbox for a
boolean AND or OR in your where clause).&nbsp; But be sure that the checkbox
never sends in SQL code, it should just send in a 1 if checked.&nbsp; Then
in your code, you check for that value and you manually program the 2
alternative versions of the SQL statement.&nbsp; It's more work in design,
but it's easier in the long run.<br>
<br>
As for escaping the data still being likely to cause problems, if you
escape all of the quotes in your data, then the data can never be
outside of one of your quoted columns of data.&nbsp; If the user sends in
the data "valid data' SQL code here", and you escape, that entire
string because just data, because it becomes "valid data\' SQL code
here".&nbsp; As a result, it will never be allowed outside of the column
data (ie, the entire thing will be treated as data).&nbsp; If the field is a
non-character field, you can simply strip out all quotes, and any
"data" that is invalid will be rejected by the PostgreSQL engine.&nbsp; For
example, if a date field is what the user is trying to compromise, and
you remove the quotes, the system will just give an error saying
invalid date format.&nbsp; This is, of course, if you don't want to do
software validation (i.e. basic data reasonability checks).<br>
<br>
Personally, I don't trust the users to give me valid SQL fragments, so
I only treat inbound data as data.&nbsp; Users are dangerous, and users with
a little knowledge are more dangerous, so I don't want my users to have
a need or desire to learn SQL.<br>
<pre class="moz-signature" cols="72">--
Thanks,
Laura Vance
Systems Engineer
Winfree Academy Charter Schools, Data-Business Office
1711 W. Irving Blvd. Ste 310
Irving, Tx &nbsp;75061
Web: <a class="moz-txt-link-abbreviated" href="http://www.winfreeacademy.com&quot;&gt;www.winfreeacademy.com&lt;/a&gt;
</pre>
</body>
</html>