How does SqlCommand sanitize parameters?

Question

Using SqlParameters is a recommended method to prevent SQL Injection in your database queries. Where can I find the code/function that internally sanitizes these parameters? I'd like to re-use this function in a custom implementation of mine. I tried to find it using Reflector, but was unsuccessful.

Hock

Great question - it is important to understand how deep is the security being provided by the use of parameterized queries. – Gluconeogenesis

Answer 1

It protects against SQL Injection, not XSS, and there is no code or function that sanitizes the parameter data.

The protection is accomplished by transmitting the parameter values to the server separately from the query string, so that the values are never substituted directly into the sql statement.

So instead of sql server running something like this:

SELECT * FROM [table] WHERE [column] = ParameterValue

It's more as if it ran something like this:

DECLARE @ParamValue int
  -- //@ParamValue variable is populated from the framework in a safe way
SELECT * FROM [table] WHERE [column] = @ParamValue

This is faster and much more secure and robust than a function that would have to evaluate the parameter data. Such a function would need to be very complex (read: error prone) to handle things like custom escape characters and future enhancements.

This neatly side steps the whole issue: data is data, code is code, and never the twain shall meet.

---

Your comment to the other, now deleted, answer:

If I pass in the value O'Rourke, it encodes it to be O''Rourke so that it doesn't break the query. Correct?

No, that is not correct. The variable is created directly from a data block, and so no special escaping or encoding is needed.

Denney

OK, that makes sense, thanks. "//@ParamValue variable is populated from the framework in a safe way" how is this populated in a safe way then? – Hock

It's not sql - the variable is created by sql server from the data block transmitted from the client, but it's kept in a data segment and never treated as code, and so the data does not need to be escaped or encoded. – Denney

Do you know of any detailed literature regarding this? I'd really like to understand the entire process. – Hock

This pretty much covers the 'what' and 'why' of it. The 'how' is something often referred to as an 'implementation detail', which means they reserve the right to change that part and it may not be published anywhere. If it is published, your best bet is in books online: msdn.microsoft.com/en-us/library/ms130214.aspx – Denney

Something I've learned since I originally wrote up this answer is the the parameterized query goes through a call to sp_executesql. Read about it to learn more about what's going on: msdn.microsoft.com/en-us/library/ms188001.aspx – Denney