SQL Injection or Server.HTMLEncode or both? Classic ASP

Question

People say to prevent SQL Injection, you can do one of the following (amongst other things):

1. Prepare statements (parameterized)
2. Stored procedures
3. Escaping user input

I have done item 1, preparing my statements, but I'm now wondering if I should escape all user input as well. Is this a waste of time seeming as I have prepared statements or will this double my chances of prevention?

Answer 1

It's usually a waste of time to escape your input on top of using parametrized statements. If you are using a database "driver" from the database vendor and you are only using parametrized statements without doing things like SQL String concatenation or trying to parametrize the actual SQL syntax, instead of just providing variable values, then you are already as safe as you can be.

To sum it up, your best option is to trust the database vendor to know how to escape values inside their own SQL implementation instead of trying to roll your own encoder, which for a lot of DBs out there can be a lot more work then you think.

If on top of this you want additional protection you can try using a SQL Monitoring Solution. There are a few available that can spot out-of-the-ordinary SQL queries and block/flag them, or just try to learn your default app behavior and block everything else. Obviously your mileage may vary with these based on your setup and use-cases.

Answer 2

Certainly the first step to prevent SQL Injection attacks is to always use parameterised queries, never concatenate client supplied text into a SQL string. The use of stored procedures is irrelevant once you have taken the step to parameterise.

However there is a secondary source of SQL injection where SQL code itself (usually in an SP) will have to compose some SQL which is then EXEC'd. Hence its still possible to be vunerable to injection even though your ASP code is always using parameterised queries. If you can be certain that none of your SQL does that and will never do that then you are fairly safe from SQL Injection. Depending on what you are doing and what version to SQL Server you are using there are occasions where SQL compositing SQL is unavoidable.

With the above in mind a robust approach may require that your code examines incoming string data for patterns of SQL. This can be fairly intense work because attackers can get quite sophisticated in avoiding SQL pattern detection. Even if you feel the SQL you are using is not vunerable it useful to be able to detect such attempts even if they fail. Being able to pick that up and record additional information about the HTTP requests carrying the attempt is good.

Escaping is the most robust approach, in that case though all the code that uses the data in you database must understand the escaping mechanim and be able to unescape the data to make use of it. Imagine a Server-side report generating tool for example, would need to unescape database fields before including them in reports.

Server.HTMLEncode prevents a different form of Injection. Without it an attacker could inject HTML (include javascript) into the output of your site. For example, imagine a shop front application that allowed customers to review products for other customers to read. A malicious "customer" could inject some HTML that might allow them to gather information about other real customers who read their "review" of a popular product.

Hence always use Server.HTMLEncode on all string data retrieved from a database.