CODEWITHSUNDEEP
sqlsql-serversecurity
user1447679
user1447679

Reputation: 3230

Security - Protecting an insert statement from malicious code

I'm building a commenting system. The comment is sent to a stored procedure in SQL.

What is the best way to prevent html, script, or SQL queries to be injected into the table? I want to do this server-side.

For example:

INSERT INTO MyTable (UserID, Comment) VALUES (@UserID, @Comment)

What would be the best way to deal with the comment field and remove any potential HTML, Scripts, or Queries to prevent attacks? Or to drop the insert if it contains certain characters? Eventually I want the user to be able to insert a link though, which would render in on the site as a clickable link...

Just new to this security stuff and obviously it's important.

Thank you so much.

Upvotes: 0

Views: 592

Answers (1)

bobince
bobince

Reputation: 536369

  1. Use parameterised statements (as you appear to be doing) with parameters for all variables and you have nothing to worry about from SQL injection.

  2. HTML and JS injections are a concern to do with the page output phase, not database storage. Trying to do HTML escaping or validation in the database layer will be frustrating and fruitless: it's not the right place to be dealing with those concerns, you'll miss or mis-handle data, and the tools for string manipulation in SQL are weak.

Don't think in terms of detecting “attacks”, because blacklists will always fail. Instead aim to handle all text correctly, and then you'll be secure as a side effect of being accurate. Variable text that you drop into an HTML file needs to be HTML-escaped; variable text that you drop into a JavaScript string literal needs to be JS-escaped.

If you're using standard .NET templates, use the <%: syntax to HTML-escape text. Use that as your output tag instead of <%= and you'll be fine. Similarly, if you're using WebForms, use the controls whose Text property is automatically HTML-escaped. (Unfortunately this is inconsistent.) Where you have to generate markup directly, use HttpUtility.HtmlEncode explicitly.

Encoding for JavaScript string literals is a little trickier. There is HttpUtility.JavaScriptStringEncode, but JS strings commonly live inside HTML <script> blocks (making the </ sequence dangerous where it isn't in native JS), or in HTML inline event handlers (where you would need to JS-encode and then HTML-encode as well). It tends to be a better strategy to encode the data you want to send to JS in the DOM using regular HTML-escaping, for example in a data- attribute or an <input type="hidden">, and have the JS grab the value from the DOM.

If you really have to allow the user to input custom markup, then you'll need to filter it at input time to a small whitelist of approved elements and attributes. Use an existing HTML purifier library.

Upvotes: 3

Related Questions