4 ms·
Ah, I am classifying a SQL statement as an output that is sent from the program to the database. Anything you put in the SQL will have to be normalised. For ex
by by 17y ago
Ah, I am classifying a SQL statement as an output that is sent from the program to the database. Anything you put in the SQL will have to be normalised.
For example, you might have a name in the database. What if someone is called O'Beirne? How do we get this into the database? Do we only allow them to have the name OBeirne?
- smackjer 17y agoEscape the quote. See http://en.wikipedia.org/wiki/SQL_injection#Preventing_SQL_injection http://en.wikipedia.org/wiki/SQL_injection#Preventing_SQL_in...
- tptacek 17y agoEscaping will get you killed. Do you know what %u2032 means? +ACc-? %ef%bd%b1? Parameterize the query.
- brlewis 17y agoIgnoring the part about PHP not knowing the difference between a byte and a character...%2032? There are databases where something other than ASCII single quote will terminate a string?
- tptacek 17y agoSure. It depends on the database, the way the query is constructed, and the way the handle is initialized. But the bigger problem is what the web stack does to the query before it hits the database. People have been playing charset games to get past SQL quoting for almost 10 years now, and not just in PHP.
- generalk 17y agoSure, that's fine. But what if someone passes you an email address that's really 500 comma-separated email addresses? What if someone passes 'admin=true' to your user update URL? Your output sanitization doesn't do much for those.
- by 17y agoYou are quite right, you do need to white-list the valid inputs for reasons other than SQL injection. I did not explain very clearly. What I was trying to say was that output normalisation will prevent SQL injection attacks. I am considering the components of the SQL statement, such as the string O'Beirne, to be things that have to be normalised before being output to the database in the SQL statement. This output normalisation, as the other replies to my post have correctly said, is best done for you by a library. It cannot be done properly by restricting the inputs, as my O'Beirne example shows, only by output normalisation of the SQL.
- tptacek 17y agoBut output normalization will not prevent SQL Injection attacks, so I'm pretty unclear on what you're trying to say. I think you're trying to say that content neutralization (turning ' into ", for instance) stops SQLI. It might or it might not, depending on the vector (tablespace injection doesn't care about metacharacters, for instance). It's at least more accurate than saying "if you make sure that the web app doesn't spit out [!@#$%^&*(){}:"<>?] you're safe".
- by 17y agoMaybe the words 'output normalisation' are the point of confusion. I am using them as in this thread http://www.reddit.com/r/programming/comments/86kgp/xss_cross_site_scripting_prevention_cheat_sheet/c08e4tm http://www.reddit.com/r/programming/comments/86kgp/xss_cross... which is the context of my quote of larholm above and is a discussion about this page http://www.owasp.org/index.php/XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet http://www.owasp.org/index.php/XSS_(Cross_Site_Scripting)_Pr... Perhaps this is not common usage, but within this context I believe I am correct in saying output normalization is what prevents SQL injection. larholm goes on to say: "The lack of output normalization IS the security vulnerability." "You can either normalize your output for each specific location as you encounter it, or normalize your input once in advance for all current and future output locations." "The former beats the latter, as it is impossible for you to know how the data will be output in the future." which also seems correct. What is "tablespace injection"? I just googled it and there are no references to it anywhere. http://www.google.com/search?q=%22tablespace+injection%22&hl=en&filter=0 http://www.google.com/search?q=%22tablespace+injection%22...
- crux_ 17y agoIgnore that other person saying to escape the quote -- do as the wikipedia article they linked says, and use parametrized queries. This does all the escaping for you, and unlike escaping, makes it much less likely that something will slip between the cracks. (There's almost never a good reason to construct SQL on the fly.)
- fendale 17y agoExactly correct - and if you are using Oracle and not using parameterised queries (known as bind variables in the Oracle world), you are quickly going to have performance problems too. That's not the case with MYSQL as it doesn't have a cursor cache in the same way Oracle has. I have no idea about Postgres or SQLServer. A rule of thumb is that if you are concatenting user input strings into your SQL query strings you are doing it wrong.
- tptacek 17y agoRemember that parameterized queries aren't a panacea. There are plenty of things that don't parameterize well, such as table names, sort orders, and limits.
- deleted 17y ago[deleted]
- epochwolf 17y agoParametrized queries still use escaping. The only difference is instead of you doing the escaping and query construction you're letting someone else's code handle it for you. (Code I would assume is far better tested than yours)
- crux_ 17y agoa) More importantly, they enforce a discipline on you: never treat data as code. b) I'm guessing there are plenty of databases that execute parametrized queries entirely differently than a plaintext ones, including the absence of an escaping step. Since the whole point of quoting + escaping is to demarcate the "data" parts of a query, and you've already done that with parameters, there's no reason a query engine can't use the parameter directly rather than run it through a useless escape then parse-and-unescape cycle.