SQL Injection or Server.HTMLEncode or both? Classic ASP

Question

People say that to prevent SQL Injection, you can do one of the following (by the way):

• Prepare statements (parameterized)
• Saved Procedures
• Exit user input

I made point 1 while preparing my statements, but now I am wondering if I should avoid entering all users. Is it a waste of time when I prepared the statements or does it double my chances of prevention?

Answer 1

Of course, the first step to prevent SQL Injection attacks is to always use parameterized queries, never concatenate the text provided by the client into an SQL string. Using stored procedures does not matter if you take the step to parameterize.

However, there is a secondary source of SQL injection where the SQL code itself (usually in SP) will need to be some SQL, which is then EXEC'd. Therefore, it can still be useful for injection, although your ASP code always uses parameterized queries. If you can be sure that none of your SQL does this and will never do it, then you are safe enough from SQL Injection. Depending on what you are doing and which version of SQL Server you are using, there are times when SQL SQL compilation is inevitable.

Based on the foregoing, a robust approach may require that your code examine incoming string data for SQL templates. This can be quite intense work, because attackers can become quite complex in preventing the discovery of SQL patterns. Even if you feel that the SQL you are using is unsafe, it is useful to be able to detect such attempts, even if they fail. The ability to select and record additional information about the HTTP requests that attempt is good.

Escaping is the most reliable approach, in this case all code that uses the data in your database must understand the transition mechanism and be able to cancel the data in order to use it. Imagine, for example, a server-side reporting tool, you will need to use the unescape database fields before including them in the reports.

Server.HTMLEncode prevents a different form of injection. Without it, an attacker could inject HTML (including javascript) into the output of your site. For example, imagine a storefront application that allows customers to view products for other customers to read. A malicious “client” might add some HTML code that could allow them to collect information about other real clients who read the “review” of a popular product.

Therefore, it always uses Server.HTMLEncode for all string data received from the database.

Answer 2

This is usually a waste of time to avoid entering data on top of using parameterized statements. If you use the "driver" of the database from the database provider, and you are only using parameterized without performing tasks such as joining SQL strings or trying to parameterize the actual SQL syntax, and not just providing variable values, then you are already as safe as you can to be.

To summarize, your best bet is to trust the database provider to know how to avoid the values ​​inside your own SQL implementation, instead of trying to collapse your own encoder, which can be a lot more work for many databases, you think.

If you need extra protection, you can try using SQL Monitoring Solution. There are several available that can recognize regular SQL queries and block / mark them, or just try to learn the default behavior of the application and block everything else. Obviously, your mileage may vary depending on your settings and use cases.

Answer 3

That day, when I had to do classic ASP, I used both methods 2 and 3. I liked the performance of stored procedures better, and this helps prevent SQL injection. I also used a standard set of inclusions to filter user input. To be truly secure, don't use classic ASP, but if you needed to, I would do all three.

Answer 4

Firstly, with injections in general:

Both last 2 have nothing to do with the injection. And the first does not cover all possible problems.

• The prepared statements are in order until you have to deal with identifiers .
• Preserved items are also vulnerable to injection. This is not an option at all.
• "escaping" "user input" is the funniest of all.

First, I assume that escaping is for strings only, not for any user input. Exiting all other types is completely useless and will not protect anything.
Then, when talking about strings, you should avoid all of them, and not just from user input.
Finally - no, you do not need to use any shielding if you use prepared instructions

Now to your question.

As you can see, HTMLEncode does not contain the word "SQL" in it. It can be assumed that Server.HTMLEncode has absolutely nothing to do with SQL injections.

This is more like another warning called XSS. This seems like a more appropriate action here and should really be used for unreliable user input.

So, you can use Server.HTMLEncode along with prepared statements. But just remember that these are completely different attacks.

You can also use HTMLEncode before the actual HTML output, and not during data storage.