Mark Burnham wrote:
I pass in parameters via the querystring, a macro loads a page that dumps SQL Server query results to XML and use XSLT to parse the results. This works nicely, but may not be the most efficient way to do this. And I understand that maybe I should be using a library to do this.
Sounds good, but make sure to watch for SQL injection here. For the benefit of others, suppose you have a drop down which populates a page by selecting the product category, the query might look like:
SELECT * FROM products WHERE product_type = 'eggchairs';
...the URL to do this becomes something like:
/mypage.aspx?category=eggchairs
...where the category is placed into the SQL query. BUT, if I re-write the URL to:
/mypage.aspx?category=eggchairs';%20exec..master%20xp_cmdshell%20'net%20user%20john%20john%20/add';%20exec..master%20xp_cmdshall%20'net%20localgroup%20administrators%20john%20/add';--
...I am able to append other SQL commands onto the end of the SELECT statement - I've now created a new administrator on your computer called "john", and can now easily take over your life... :dontgetit:
Solution:
1) Escaping single quotes helps (replace ' with '') BUT there are plenty of other ways to inject SQL... Integers are a particular problem, so need to be cast as such.
2) Best bet is to build a dedicated library of functions where the SQL calls are hard coded AND
parameterised (google if you don't know). This largely(-ish!) solves the problem because parameters are passed to the server separately and the SQL interpreter cannot mistake parameters for SQL commands.
For example, you might now want to create: mylibrary.lookupProductsByType(string productType) instead of mylibrary.doGenericSqlCall(string query)
Stewart
Bawden Quinn Associates
UK
Bawden Quinn Associates, UK