http://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks
Golden Rules:
3: Custom-Written Value Sanitation. Avoid writing your own sanitation routines as much as possible except when it’s absolutely the only option remaining (which is very unlikely in any modern language). Input sanitation is a hard problem, and the costs of getting it wrong are huge. It’s best to leave that job to someone else. When you are forced to rely on this method use white-listing rather than black-listing to sanitize input.
2: Framework / Library Based Value Sanitation. Leave the sanitation routines to the domain experts. Sanitation routines in 1st party frameworks and libraries are typically created by the same folks that wrote the DB or the SQL API. They have a much higher chance of knowing, and properly handling, all of the edge cases than you do. An example of this technique would be using php’s mysql_escape_string() function to sanitize values that will be inserted into strings that serve as dynamic SQL statements.
1: Parameter Binding. Instead of constructing a SQL statement as a raw string which includes user data as in-place literal values, create a SQL statement with tokens where the user data would be. Then bind the user supplied data to the appropriate parameters. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement. This can be used in conjunction with library based input sanitation as well.
http://www.tcl.tk/community/tcl2004/Tcl2003papers/cleverly.htm
Luckily for the Tcl programmer who craves laziness, OpenACS provides a higher-level database API on top of ns_db that hides all the mundane house-keeping chores. Additionaly, bind-variable emulation is available which frees the programmer from worrying about always escaping apostrophes in SQL statements. Bind variables are prefaced with a colon.
set name "Jim O'Connor"
set salary 40000
db_dml new_hire {
insert into employees (name, salary, dept)
values (:name, :salary, 'Accounting')
}
http://openacs.com/forums/message-view?message_id=250625
Posted by Dave Bauer on 01/19/05 04:57 PM
In Postgresql the db_* commands extract the “bind” variables and SQL quote them automatically. In the PG driver itself the [] charactaers are escaped as well for safety.
This means that the effective security of PostgreSQL is the same as Oracle.
This is done at the database api layer.
In addition ad_page_contract has filters and validation features to check input. ad_form and the form builder also have validation features for input.
0 Comments.