Re: sql injection and php

From: Sverre H. Huseby (shh@thathost.com)
Date: 05/29/02 

Date: Wed, 29 May 2002 20:14:35 +0200
From: "Sverre H. Huseby" <shh@thathost.com>
To: Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>

[Jacek Lach]

| Does the magic_quotes in php's configuration resolves the problem
| of sql injection?

[Florian Weimer]

| It depends. If your database uses the same escaping strategy as
| PHP, it may be safe.

If that is to be interpreted as "go ahead, just pass unchecked data to
the database", I would like to state the following:

I have seen several web applications the last couple of years (since I
jumped from being a developer to being an application security
consultant) that are terribly insecure because programmers have a
relaxed view on input validation and meta character escaping. Far too
many developers think that "if I can't find a way to exploit my code,
nobody else can". That view is extremely dangerous. There may always
be an intruder that is more creative when it comes to malicious
thinking than the developer is herself.

The reason this view is dangerous, is that the average developer is
not trained in destructive thinking. He works by constructing things.
Some developers have seen samples on illegal login using "' --" (quote
dash dash), but they haven't thought about where the danger in such a
construct lies. Many developers want to solve the problem by getting
rid of those dashes (SQL comments). The smarter ones understand that
the quote character is the problem. And the ones with most insight
understand that in general, this has to do with passing data between
different interpretation contexts. (I don't invent this scenario of
ignorance, I've given courses and lectures on this topic for several
hundred developers in various consulting companies in Norway the last
couple of years. The scenario is based on the feedback I get when I
show them examples of SQL Injection.)

My advice is: One should always be paranoid. Newer trust the lower
layers to handle the security for you. Never underestimate the
potential attacker. Always validate the data, and always make sure
the data has an appropriate format for the layer you pass them to.

"The appropriate format" is what is often difficult. PHP may escape
quotes for you. When passing data to an SQL server (any database
server using SQL, not just the one Microsoft was rude enough to call
SQL Server), the quotes only have a special meaning inside a string
context. You do not always pass data to an SQL server as a string.
When the SQL server parses you statement and reads

   SELECT * FROM Foo WHERE Bar=1...

and has just come to the elipsis (the dots), it is in a numeric
context: It parses a number. Escaping of quotes will not be enough,
as any non-numeric character will make the SQL server leave the
numeric context. If the assumed number was coming from eg. a URL
paramter, the attacker would be able to pass

  1; DELETE FROM Foo

The result would be damaging, even if PHP automaticallu escapes
quotes, as there are no quotes in this query.

To solve these problems, programmers will have to think. They will
have to understand how the next layer (in this example, the SQL
server) is going to parse the passed data. And they will have to make
sure the data passed does not contain anything that makes the next
layer change context.

The same principle goes for any passing of data to sub-systems.
Before SQL databases were used as examples, Unix command exection from
Perl CGI was used. People didn't want anyone to insert "; rm -rf /"
and have that executed by the shell. Nowadays, data is passed to XML
parsers, XPath parsers, and who knows what. The same things apply:
Data coming from the outside should not allow the next layer parser to
switch context.

Even Cross-site Scripting (XSS) is covered by this rule. In XSS, the
"next layer parser" is the HTML parser of the end user's browser.

Oh well, this was far more than I initially intended to write. Sorry.
I'm celebrating my own birthday with, well, some beverages not
intended for minors. I hope at least some will be able to understand
anyway. :)

Sverre. 

-- 
shh@thathost.com			Computer Geek?  Try my Nerd Quiz
http://shh.thathost.com/		http://nerdquiz.thathost.com/

Relevant Pages

  • Bad data
    ... I am creating a DTS process that will extract data from a ... text file and place it into sql server. ... double quotes must be off, because when I try to tell sql ...
    (microsoft.public.sqlserver.dts)
  • Re: Login failed for user (null). Not associated with a trusted SQLSe
    ... If your developers have IIS and SQL installed on the same box the user token for SSPI will be passed to SQL for the login. ... The simplest solution will be to add and SQL server username and password to the connection string. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: crash on virtual dimensions
    ... SQL authentication doesn't work for SQL Server to anywhere. ... It is basically a way for access developers to start writing all of their ...
    (microsoft.public.sqlserver.olap)
  • Re: Opinions on 3rd party code generators?
    ... The high level documentation appears to suggest they're ... How had is it to write Select InvoiceID, ... They claim you don't need to know SQL to use their tools. ... developers don't understand SQL. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Inconsistent Search Results
    ... You're using SQL Server 2000 SP3 on Windows Server ... When you pass the above string into the @searchstreet variable are you also ... passing in double quotes? ...
    (microsoft.public.sqlserver.fulltext)