Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why we do SQLiteCommand, Parameters.add while we can use string.Format to compose sql statement?

Tags:

c#

sqlite

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?

like image 270
SuperMENG Avatar asked Nov 16 '13 11:11

SuperMENG


People also ask

Why use parameters in SQL?

Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes.

Does String format prevent SQL injection?

Simply using String. Format does not protect against SQL injection attacks.

How do I add multiple parameters to SqlParameter?

public IEnumerable<SampleModel> RetrieveSampleByFilter(string query, params SqlParameter[] parameters) { using(var connection = new SqlConnection(dbConnection)) using(var command = new SqlCommand(query, connection)) { connection. Open(); if(parameters. Length > 0) foreach(var parameter in parameters) command.

What is the use of CMD parameters AddWithValue?

AddWithValue replaces the SqlParameterCollection. Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.


1 Answers

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

like image 187
Jon Skeet Avatar answered Oct 18 '22 11:10

Jon Skeet