16

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?

jordanz
  • 367
  • 4
  • 12
SuperMENG
  • 439
  • 4
  • 7
  • 15

1 Answers1

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

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 1
    And avoid errors in string quoting just as the OP has done in its example – Steve Nov 16 '13 at 11:15
  • It provides an easy and fast way to parameterize queries. This yields bulletproof and simple code that accesses data. – saeed Nov 16 '13 at 11:17
  • It provides an easy and fast way to parameterize queries. This yields bulletproof and simple code that accesses data. – saeed Nov 16 '13 at 11:17
  • 1
    @Steve: I was assuming that the "..." would include the closing quote in the real query. But yes, point taken :) – Jon Skeet Nov 16 '13 at 11:24
  • 1
    Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure – saeed Nov 16 '13 at 11:26
  • 1
    @saeed: Isn't that covered by "avoiding SQL injection attacks"? – Jon Skeet Nov 16 '13 at 11:32
  • @JonSkeet of course it does! but I want to emphasis more on `not a part of SQL statement` – saeed Nov 16 '13 at 11:43