Security

One reason to use parameterized queries is for security. Using a properly formatted parameterized query can protect against SQL injection attacks. A SQL injection attack is where a malicious user can execute database code (in this case, T-SQL) on a server by appending it to a data-entry field in the application. As an example, imagine we have an application that contains a form that asks the user to enter their name into a text box. If the application were to use an ad hoc statement to insert this data into the database, it would generally concatenate a T-SQL string with the user input, as in the following code:

DECLARE @sql nvarchar(MAX);
SET @sql = N'INSERT Users (Name) VALUES (''' + <user input> + ''');';
EXECUTE (@sql);

A malicious user might enter the Bob'); DROP TABLE Users; -- value into the text box.

If this is the case, the actual code that gets sent to SQL Server would look like the following:

INSERT Users (Name) VALUES ('Bob'); DROP TABLE Users; --');

This is valid T-SQL syntax that would successfully execute. It would first insert a row into the Users table with the Name column set to 'Bob', then it would drop the Users table. This would of course break the application, and unless there was some sort of auditing in place, we would never know what happened.

Let's look at this example again using a parameterized query. The code might look like the following:

EXECUTE sp_executesql @stmt = N'INSERT Users (Name) VALUES (@name)', @params = N'@name nvarchar(100)', @name = <user input>

This time, if the user were to send the same input, rather than executing the query that the user embedded in the string, the SQL Server will insert a row into the Users table, with the Name column set to ('Bob'); DROP TABLE Users; --'. This would obviously look a bit strange, but it wouldn't break the application or breach security.