Home > Security, SQL Server, Web > How to protect against SQL injection attacks

How to protect against SQL injection attacks

SQL injection is the act of injecting some characters (e.g. SQL) into a SQL statement causing it to perform an unintended (e.g. malicious) action.  It most often occurs when some criteria fed to the query is provided by user input (or is editable by a user – say the querystring in a URL or a field on a web form).

A number of things contribute to making this type of attack possible including: 1) insecure code, 2) unnecessarily highly privileged database access accounts.

Things you can do to protect against SQL injection:

  1. DO NOT execute any T-SQL by concatenating values, particularly where user input is involved
  2. Use parameterized SQL instead – this ensures that values are escaped such that a T-SQL command cannot be terminated
  3. Use stored procedures where the T-SQL is static and criteria values are passed in as parameters (then use in conjunction with point 2 above)
  4. Do not grant your database accounts used by your application unrestricted access to databases.  DO NOT grant them DBO rights in a production OR test environment.  This is a HUGE BENEFIT of stored procedures.  Stored Procedures can be granted execute rights and SQL Server’s chaining system (known as ownership chains) will automatically allow those operations to run against objects (e.g. tables) without explicit rights there.  Grant rights directly to tables only if application code must run SQL directly.  Even then pay attention to SELECT vs UPDATE rights.

References:

  1. How To: Protect from SQL Injection in ASP.NET (MSDN article from Microsoft Patterns & Practices)
  2. SQL Injection (MSDN article with great tips on protecting against this)

-Krip

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: