TalkPHP - View Single Post

Village Idiot · 12-09-2007, 04:49 AM

Quote:

Originally Posted by Salathe

That entirely depends on the method used to 'clean' the incoming data. Any decent filtering method will take this situation into account and protect your queries -- Village Idiot, if your current cleaning method does not cater for this situation then it's time to rethink things. Inventing a rule to wrap everything in quotes is just putting a band-aid over the problem rather than treating the injury properly.

String values should be wrapped in quotes; integer, double, etc. columns should not be wrapped in quotes. It's just good SQL.

After doing allot of looking around on google regarding that, here are basic ways of doing this and their problems (please correct me if I missed something)

1. Check all numeric/floating/ect. before running the query with is_numeric, die with error if not numeric.
Problem: Future developers may not be so wise to catch on to what you are doing. You may also forget once.

2. Have another cleaning function for expected numeric variables
Problem: Same as 1

3. Quote everyting
Problem: Same as 1 and 2, but much simpler. It is the difference of 2 characters opposed to a different or extra function.

Its not bad SQL, as taken from http://dev.mysql.com/doc/refman/5.0/...uidelines.html

Quote:

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.