Why do we use SQLiteCommands instead of string.Format to compose SQL statements?

Question

Many tutorials I've seen compose SQL statements by using variables and Parameters.Add, like 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)

instead?

Jon Skeet · Accepted Answer · 2013-11-16 11:40:43Z

40

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.)

edited Nov 16, 2013 at 11:40

answered Nov 16, 2013 at 11:11

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Steve Over a year ago

And avoid errors in string quoting just as the OP has done in its example

saeed Over a year ago

It provides an easy and fast way to parameterize queries. This yields bulletproof and simple code that accesses data.

Jon Skeet Over a year ago

@Steve: I was assuming that the "..." would include the closing quote in the real query. But yes, point taken :)

saeed Over a year ago

Anything 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 a year ago

@saeed: Isn't that covered by "avoiding SQL injection attacks"?

|

Collectives™ on Stack Overflow

Why do we use SQLiteCommands instead of string.Format to compose SQL statements?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related