How do SQL parameters work internally?
A co-worker and I were browsing SO when we came across a question about SQL Injection and we were wondering: How do parameterized queries work internally? Does the API you are using (assuming it supports parameterized queries) do the concatenation by combining the query with parameters? Or do the parameters go to the SQL engine separately from the query and no concatenation is done at all?
Google didn't help much, but maybe we weren't looking for the right thing.
source to share
The parameter makes it intact on the SQL server and is individually "packaged" with metadata indicating its type, whether it be input or output, etc. As Alex Reitbort points out, this is because parameterized statements are a server-level concept, not just a convenient way to invoke commands from different connection levels.
source to share
I doubt SQL SERVER is building a complete query string from a given parameterized query where the parameter list is concatenated.
It most likely parses a given parameterized command line, dividing it into an internal data structure based on reserved words and characters (SELECT, FROM, ",", "+", etc.). Inside this data structure, there are properties / places for values like table names, literals, etc. This is where it copies (verbatim) each parameter passed (from the list) to the appropriate section of this structure.
so your @UserName value is: 'x'; deleting users -
should never be avoided, just used as the literal meaning it really is.
source to share
Parameters are passed along with the request (not in the request) and are automatically escaped by the API as they are sent according to the underlying database communication protocol.
For example, you might have
Query: <<<<select * from users where username = :username>>>>
Param: <<<<:username text<<<<' or '1' = '1>>>>>>>>
It's not the exact encoding that any database protocol uses, but you get the idea.
source to share