What Is Parameterized Query

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

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.

Confusion between prepared statement and parameterized query in Python

  • Prepared statement: A reference to a pre-interpreted query routine on the database, ready to accept parameters

  • Parametrized query: A query made by your code in such a way that you are passing values in alongside some SQL that has placeholder values, usually ? or %s or something of that flavor.

The confusion here seems to stem from the (apparent) lack of distinction between the ability to directly get a prepared statement object and the ability to pass values into a 'parametrized query' method that acts very much like one... because it is one, or at least makes one for you.

For example: the C interface of the SQLite3 library has a lot of tools for working with prepared statement objects, but the Python api makes almost no mention of them. You can't prepare a statement and use it multiple times whenever you want. Instead, you can use sqlite3.executemany(sql, params) which takes the SQL code, creates a prepared statement internally, then uses that statement in a loop to process each of your parameter tuples in the iterable you gave.

Many other SQL libraries in Python behave the same way. Working with prepared statement objects can be a real pain, and can lead to ambiguity, and in a language like Python which has such a lean towards clarity and ease over raw execution speed they aren't really the greatest option. Essentially, if you find yourself having to make hundreds of thousands or millions of calls to a complex SQL query that gets re-interpreted every time, you should probably be doing things differently. Regardless, sometimes people wish they could have direct access to these objects because if you keep the same prepared statement around the database server won't have to keep interpreting the same SQL code over and over; most of the time this will be approaching the problem from the wrong direction and you will get much greater savings elsewhere or by restructuring your code.*

Perhaps more importantly in general is the way that prepared statements and parametrized queries keep your data sanitary and separate from your SQL code. This is vastly preferable to string formatting! You should think of parametrized queries and prepared statements, in one form or another, as the only way to pass variable data from your application into the database. If you try to build the SQL statement otherwise, it will not only run significantly slower but you will be vulnerable to other problems.

*e.g., by producing the data that is to be fed into the DB in a generator function then using executemany() to insert it all at once from the generator, rather than calling execute() each time you loop.

tl;dr

A parametrized query is a single operation which generates a prepared statement internally, then passes in your parameters and executes.

edit: A lot of people see this answer! I want to also clarify that many database engines also have concepts of a prepared statement that can be constructed explicitly with plaintext query syntax, then reused over the lifetime of a client's session (in postgres for example). Sometimes you have control over whether the query plan is cached to save even more time. Some frameworks use these automatically (I've seen rails' ORM do it aggressively), sometimes usefully and sometimes to their detriment when there are permutations of form for the queries being prepared.

Also if you want to nit pick, parametrized queries do not always use a prepared statement under the hood; they should do so if possible, but sometimes it's just formatting in the parameter values. The real difference between 'prepared statement' and 'parametrized query' here is really just the shape of the API you use.

how to change sql statement to parameterized query?

I have the answer. c.CurrencyCode = '" + Code.Replace("'", "''") + "' simply changes to c.CurrencyCode = ?code

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.

What happens to a parameterized query at database level

I cannot answer 1). But you can pass the parameter names as well as a varying number of parameters to sp_executesql (Transact-SQL). So sp_executesql is not a limiting factor here.


  1. The database does not create a concatenated string for parametrized queries. It compiles the SQL command string as is, i.e. with the parameter names and produces an executable query. You can think of it as a method. The parameter values are then passed to this "method" as real parameters.

Besides defeating SQL injection, this has the advantage that the database can cache the compiled query and reuse it the next time the same SQL command is executed, even with different parameter values.

Yet another advantage is that you don't have to care about the right representation of literals. This is especially valuable for date/time literals (which tends to be quite complicated because formats are culture specific and can vary otherwise). You don't need to care about escaping quotes in strings.

Is this parameterized query open to SQL injection?

This query is not open to SQL injection, because it uses a parameterized query. The data is not substituted for the parameter ($1), but sent separately in a “bind” message, so no matter what the data contain, it is not interpreted as part of the SQL statement.

Moreover, it looks like the argument is an integer, and SQL injection can only happen with string arguments.



Related Topics



Leave a reply



Submit