Re: Sql injection attacks

From: Laura Vance <vancel(at)winfreeacademy(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Sql injection attacks
Date: 2004-07-26 15:42:30
Message-ID: 41052666.50800@winfreeacademy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!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(dot)fsf(at)asmodeus(dot)mcnaught(dot)org">
<pre wrap="">Geoff Caplan <a class="moz-txt-link-rfc2396E" href="mailto:geoff(at)variosoft(dot)com">&lt;geoff(at)variosoft(dot)com&gt;</a> writes:

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

DM&gt; Geoff Caplan <a class="moz-txt-link-rfc2396E" href="mailto:geoff(at)variosoft(dot)com">&lt;geoff(at)variosoft(dot)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">www.winfreeacademy.com</a>
</pre>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.6 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2004-07-26 16:22:42 Re: Sql injection attacks
Previous Message Geoff Caplan 2004-07-26 15:33:31 Re: Sql injection attacks