4 Answers
4

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed becase the SQL is never injected into the resulting statement.

@sqlchild: table, column or procedure names (well identifiers in general) cannot be parameterized, only "values" or "arguments" (as OJ said). Like used in WHERE or JOIN/IN clauses, parameters to functions or procedures, etc. Hence the name "parameterized" queries.
–
Christian.KMar 29 '11 at 6:12

1

@Christian.K : sir, i meant that, if i pass DROP table Cars in the textbox , to an insert statement, insert into cars values(@carname); cmd.Parameters.Add(@carname,SQLDBTYPE varchar), then what would the parameterized query do ?
–
sqlchildMar 29 '11 at 6:20

3

@sqlchild: Sorry, misunderstood that. It would just insert the string "DROP table Cars" in the respective column/field.
–
Christian.KMar 29 '11 at 6:23

2

@sqlchild: No it is not known as escaping. It is a mechanism provided by the database to make escaping unnecessary. I think you need to read up a little more background on SQL injection (e.g. here en.wikipedia.org/wiki/SQL_injection) - more than fits in a comment or in a response to your original question.
–
Christian.KMar 29 '11 at 7:11

1

@sqlchild: Your starting to change the topic and have already accepted the answer to your original question anyway. Please consider posting another/new question about other things you need to know. This is how SO is supposed to work and allows for better discoverabilty and benefits everyone.
–
Christian.KMar 29 '11 at 7:34

but when sql takes the value from the parameter, then how does it reads it, i mean it would take it in the same way as directly from the string , i.e. it would read it as drop table mytable , or am i wrong?
–
sqlchildMar 29 '11 at 6:13

@ sqlchild :nope...it escapes all the ' with '' so that the entire string becomes the parameter...let me add this to the answer
–
MulkiMar 29 '11 at 6:16

With parametrized queries, in addition to general injection, you get all the data types handled, numbers (int and float), strings (with embedded quotes), dates and times (no formatting problems or localization issues when .ToString() is not called with the invariant culture and your client moves to a machine with and unexpected date format).

Parameterized queries allow the client to pass the data separately form the query text.
Where on most free from text you would do validation + escaping.
Of course Parameterization don't help against other kind of injection, but as the parameter are passed separately, they are not use as execution text query.

A good analogy would be the "recent" execution bit used with most of the modern processor and Operating system to protect from buffer overflow. It still allows the buffer overflow but prevent the execution of the injected data.