How to Create a Parameterized SQL Query? Why Should I

How do I create a parameterized SQL query? Why Should I?

The EXEC example in the question would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:

';DROP TABLE bar;--

Try putting that in your fuz variable (or don't, if you value the bar table). More subtle and damaging queries are possible as well.

Here's an example of how you do parameters with Sql Server:

Public Function GetBarFooByBaz(ByVal Baz As String) As String
    Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@Baz", SqlDbType.VarChar, 50).Value = Baz
        Return cmd.ExecuteScalar().ToString()
    End Using
End Function

Stored procedures are sometimes credited with preventing SQL injection. However, most of the time you still have to call them using query parameters or they don't help. If you use stored procedures exclusively, then you can turn off permissions for SELECT, UPDATE, ALTER, CREATE, DELETE, etc (just about everything but EXEC) for the application user account and get some protection that way.

What is parameterized query?

A parameterized query (also known as a prepared statement) is a means of pre-compiling a SQL statement so that all you need to supply are the "parameters" (think "variables") that need to be inserted into the statement for it to be executed. It's commonly used as a means of preventing SQL injection attacks.

You can read more about these on PHP's PDO page (PDO being a database abstraction layer), although you can also make use of them if you're using the mysqli database interface (see the prepare documentation).

Why do we always prefer using parameters in SQL statements?

Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a program interface such as a desktop program or web site.

In your example, a user can directly run SQL code on your database by crafting statements in txtSalary.

For example, if they were to write 0 OR 1=1, the executed SQL would be

 SELECT empSalary from employee where salary = 0 or 1=1

whereby all empSalaries would be returned.

Further, a user could perform far worse commands against your database, including deleting it If they wrote 0; Drop Table employee:

SELECT empSalary from employee where salary = 0; Drop Table employee

The table employee would then be deleted.

---

In your case, it looks like you're using .NET. Using parameters is as easy as:

string sql = "SELECT empSalary from employee where salary = @salary";

using (SqlConnection connection = new SqlConnection(/* connection info */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    var salaryParam = new SqlParameter("salary", SqlDbType.Money);
    salaryParam.Value = txtMoney.Text;

    command.Parameters.Add(salaryParam);
    var results = command.ExecuteReader();
}

Dim sql As String = "SELECT empSalary from employee where salary = @salary"
Using connection As New SqlConnection("connectionString")
    Using command As New SqlCommand(sql, connection)
        Dim salaryParam = New SqlParameter("salary", SqlDbType.Money)
        salaryParam.Value = txtMoney.Text

        command.Parameters.Add(salaryParam)

        Dim results = command.ExecuteReader()
    End Using
End Using

Edit 2016-4-25:

As per George Stocker's comment, I changed the sample code to not use AddWithValue. Also, it is generally recommended that you wrap IDisposables in using statements.

Parameterize SQL Queries

To have a parameterized query you need to create parameters and write a proper SQL text where, in place of values typed directly from your user, you have parameter placeholders.

So, for example, you sql text should be something like this

Dim sqlText = "SELECT ID FROM accounts WHERE accountname =@name AND password=@pwd"

Now you have a parameterized text, but stil we need to create the parameters that will be sent to the database engine together with your sql command.

You can create the parameter (two in this case) in this way before calling the method that executes the query

Dim p1 as MySqlParameter = new MySqlParameter("@name", MySqlDbType.VarChar)
p1.Value = user  

Dim p2 as MySqlParameter = new MySqlParameter("@pwd", MySqlDbType.VarChar)
p2.Value = password

Dim pms As List(Of MySqlParameter) = new List(Of MySqlParameter)()
pms.Add(p1)
pms.Add(p2)

Now we need to pass this list to your method (and this requires changes to your method signature)

DatabaseConnecter.readField(sqlText, pms)

The method itself should change to something like

Public Function readField(ByVal sql As String, Optional pms As List(Of MySqlParameter) = Nothing) As String
    Dim output As String = "ERROR"
    Using cn = New MySqlConnection(connString.ToString())
        Using cmd = New MySqlCommand(sql, cn)
            cn.Open()

            ' This block adds the parameter defined by the caller to the command
            ' The parameters are optional so we need to check if we have really received the list or not
            if pms IsNot Nothing Then
                cmd.Parameters.AddRange(pms.ToArray())
            End If
            Using rd = cmd.ExecuteReader()
                Try
                    rd.Read()
                    output = rd.GetString(0)
                    rd.Close()
                Catch ex As Exception
                End Try
            End Using
            ' no need to close when inside a using block
            ' cn.Close()
        End Using
    End Using
    Return output
End Function

The method now has an optional parameter that will contain the list of the parameters required by the query (or nothing if your query doesn't require parameters). This list is added to the command parameters collection and the query is now executed.

Final Note: Storing passwords in clear text into a database is a well known security problem. I suggest you to search about how to store passwords in a database.

How do parameterized queries help against SQL injection?

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 because the SQL is never injected into the resulting statement.

How to create a Dynamic Parameterized SQL Query String in C#

[C#]

You can use Table-valued parameters to send multiple rows in a single SQL query.
The flow would be

• Define a table type. The schema would be same as the parameters to be inserted.
• Create a DataTable with the exact same names and types of the table type.
• Pass the DataTable as parameter in the query.

Sample

CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
            Direction = ParameterDirection.Input,
        });

    cmd.ExecuteNonQuery();
}

Reference : https://docs.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching#table-valued-parameters

[JAVA]

You can use PreparedStatement, create batch of the rows (ps.addBatch()) to be inserted and insert batch in one go (ps.executeBatch()).

Sample :

PreparedStatement ps= con.prepareStatement("INSERT INTO Sample VALUES (?, ?, ?, ?)");

for(int i; i<10; i++){
  ps.setString(1, "String1");
  ps.setString(2, "String2");
  ps.setString(3, "String3");
  ps.setInt(4, 1000);

  ps.addBatch();
}

ps.executeBatch();

If there are a lot of records to be inserted, you can create multiple batches and insert them inside the loop itself.

SQL Server parameterized SQL query - do I still need to escape quotes?

No, you do not need to escape quotes. When you perform a SQLCommand using SQLParameters, the parameters are never inserted directly into the statement.

Instead, a system stored procedure called sp_executesql is called and given the SQL string and the array of parameters (the TDS protocol).

The parameters are isolated and treated as data. This mitigates SQL injection concerns and provides other benefits, such as strong-typing and improved performance.

---

Related Topics