Re: Sql injection attacks - Mailing list pgsql-general

Doug McNaught wrote:<br /><blockquote cite="mid877jsq7seo.fsf@asmodeus.mcnaught.org" type="cite"><pre wrap="">Geoff
Caplan<a class="moz-txt-link-rfc2396E" href="mailto:geoff@variosoft.com"><geoff@variosoft.com></a> writes:
 
 </pre><blockquote type="cite"><pre wrap="">Doug,

DM> Geoff Caplan <a class="moz-txt-link-rfc2396E" href="mailto:geoff@variosoft.com"><geoff@variosoft.com></a>
writes:
   </pre><blockquote type="cite"><blockquote type="cite"><pre wrap="">But in web work, you are often using GET/POST
datadirectly 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> Can you give an example of this that isn't also
anexample 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:   </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,
butnever accept raw SQL from the user.  It's easy enough to check for certain fields (such as a checkbox for a boolean
ANDor OR in your where clause).  But be sure that the checkbox never sends in SQL code, it should just send in a 1 if
checked. Then in your code, you check for that value and you manually program the 2 alternative versions of the SQL
statement. It's more work in design, but it's easier in the long run.<br /><br /> As for escaping the data still being
likelyto cause problems, if you escape all of the quotes in your data, then the data can never be outside of one of
yourquoted columns of data.  If the user sends in the data "valid data' SQL code here", and you escape, that entire
stringbecause just data, because it becomes "valid data\' SQL code here".  As a result, it will never be allowed
outsideof the column data (ie, the entire thing will be treated as data).  If the field is a non-character field, you
cansimply strip out all quotes, and any "data" that is invalid will be rejected by the PostgreSQL engine.  For example,
ifa date field is what the user is trying to compromise, and you remove the quotes, the system will just give an error
sayinginvalid date format.  This is, of course, if you don't want to do software validation (i.e. basic data
reasonabilitychecks).<br /><br /> Personally, I don't trust the users to give me valid SQL fragments, so I only treat
inbounddata as data.  Users are dangerous, and users with a little knowledge are more dangerous, so I don't want my
usersto 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  75061
Web: <a class="moz-txt-link-abbreviated" href="http://www.winfreeacademy.com">www.winfreeacademy.com</a>
</pre>