+------ | Update 2004-01-05: You may read _much_ more in my newly published | book: "Innocent Code: A Security Wake-up Call for Web Programmers" | http://innocentcode.thathost.com/ +------ From: "Sverre H. Huseby" Subject: Re: sql injection and php Date: Wed, 29 May 2002 20:14:35 +0200 To: vuln-dev@securityfocus.com [vuln-dev user #1] | Does the magic_quotes in php's configuration resolves the problem | of sql injection? [vuln-dev user #2] | 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. Never 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 your 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 automatically 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.