MySQL Parameterized Queries

MySQL parameterized queries

Beware of using string interpolation for SQL queries, since it won't escape the input parameters correctly and will leave your application open to SQL injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

Incorrect (with security issues)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

Correct (with escaping)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

It adds to the confusion that the modifiers used to bind parameters in a SQL statement varies between different DB API implementations and that the mysql client library uses printf style syntax instead of the more commonly accepted '?' marker (used by eg. python-sqlite).

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 can I use a parameterized query to search on a column by its name?

This is trying to help keep you safe from bad args (prevent things like SQL injection) but isn't designed to do replacement on anything other than a value. You want it to insert a table name. Unfortunately for you the code is aware of col's type (string) and quotes it because in SQL it's nvarchar and that's how they literals are written, enclosed in single quotes. Might seem a bit like a hack but you need this instead;

db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE %s = ?", col), searchStr)

Putting the table name into your query string before passing it to Query so it doesn't get treated like an argument (ie a value used in the where clause).

MySQL parameterized query is functioning like it is not parameterized in C# application

You should use the name of the column Gherkin inside the function VALUES() and not the named parameter ?Gherkin:

UPDATE Gherkin = VALUES(Gherkin)

Use Parameterized Query with Python and MySQL

You can't use query parameters for identifiers (like a database name or table name or column name).

Query parameters can be used only in place of a constant value — a quoted string, quoted date/time, or a numeric value. Not identifiers, expressions, SQL keywords, etc.

To combine a database name with your CREATE DATABASE statement, you have to format it into the string in a way that forms the full statement before it is sent to MySQL.

db_name = "BOOKS"
create_database_query = "CREATE DATABASE %s;" % db_name

cursor.execute(create_database_query)

Because this creates a risk of SQL injection when you format variables into your string, it's up to you to make sure the db_name is safe.

---

Update: Thanks to @Parfait for the reminder about current best practices of string-formatting.

Prefer:

db_name = "BOOKS"
create_database_query = "CREATE DATABASE {};".format(db_name)

Or F-strings:

db_name = "BOOKS"
create_database_query = f"CREATE DATABASE {db_name};"

(In other words, Python has become Ruby ;-)

MySQL Parameterized query for time type

The argument must be a tuple, even if there's just one parameter:

mycursor.execute(insert_duration, (duration,))

---

Related Topics