Why we do SQLiteCommand, Parameters.add while we can use string.Format to compose sql statement?
16,437
Four reasons:
- Avoiding SQL injection attacks
- Avoiding problems with strings containing genuine apostrophes with no intention of causing a SQL injection attack (e.g. a last name of "O'Reilly"
- Avoiding string unnecessary conversions, which can cause failures for cultural reasons (e.g. the difference between "1.23" and "1,23" depending on your culture
- Keeping the code (SQL) and the data (parameters) separate for cleaner readability
Also note:
- This isn't SQLite specific. It's best practice for all databases.
-
You don't need to use
@
as a prefix to your variables unless they're keywords. So it would be more idiomatic to write:command.Parameters.Add(new SQLiteParameter("@lastName", lastName));
(Ditto for the method parameter declarations to start with... but not the parameters inside the SQL statement.)
Related videos on Youtube
Author by
SuperMENG
Updated on October 28, 2022Comments
-
SuperMENG over 1 year
I saw in many tutorial that compose sql statement by using variable and Parameters.Add likt this
public void updateStudent(String @studentID, String @firstName, String @lastName) { SQLiteCommand command = conn.CreateCommand(); command.CommandText = "UPDATE Students SET firstName = @firstName, lastName = @lastName WHERE studentID = @studentID"; command.Parameters.Add(new SQLiteParameter("@studentID", @studentID)); command.Parameters.Add(new SQLiteParameter("@firstName", @firstName)); command.Parameters.Add(new SQLiteParameter("@lastName" , @lastName)); command.ExecuteNonQuery(); }
why don't we use
string.Format("Update Students SET firstName = '{0}', lastName = '{1}...", @firstName, @lastname)
any benefit??
-
Steve over 10 yearsAnd avoid errors in string quoting just as the OP has done in its example
-
saeed over 10 yearsIt provides an easy and fast way to parameterize queries. This yields bulletproof and simple code that accesses data.
-
saeed over 10 yearsIt provides an easy and fast way to parameterize queries. This yields bulletproof and simple code that accesses data.
-
Jon Skeet over 10 years@Steve: I was assuming that the "..." would include the closing quote in the real query. But yes, point taken :)
-
saeed over 10 yearsAnything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure
-
Jon Skeet over 10 years@saeed: Isn't that covered by "avoiding SQL injection attacks"?
-
saeed over 10 years@JonSkeet of course it does! but I want to emphasis more on
not a part of SQL statement