Das deutsche Python-Forum

Code: Alles auswählen

query = "DELETE FROM address WHERE ID=%s"
data = ( id, )

cursor.execute( query, data )

Code: Alles auswählen

#!/usr/bin/python

"""
  save_sql.py

  Author: Henning Hasemann

  A little module ensuring that userdata wont
  hurt sql-strings, quoting tablenames, column names and
  values correctly.
  
  Note 1: You are allowed to just provide one dictionary instead
  of two. In this case, it will be used for both, keys and values.

  Note 2: Always use %(Kname)s / %(Vname)s, (pronouncing the last s),
  because all values are internally converted into strings.
  (i.e. it makes no sense to use %(Vname)d, as this simply wont work)
  
  Example:
  
  import MySQLdb
  from save_sql import mksql

  conn = MySQLdb.connect(db="MyDatabase", user="MyUser", paswd="MyPW", host="localhost")
  curs = conn.cursor()

  query = mksql("INSERT INTO %(Ktab)s (`name`, `surname`, %(Kcol3)s, `count`) VALUES (%(Vname)s, %(Vsur)s, 'X', %(Vcnt)s)",
      {"tab": "MyTable", "col3": "Some` really; unsafe stuff\\"},
      {"name": "'; DELETE", "sur": "even' unsafer`blorg", "cnt": 500})

  curs.execute(query)
  
  curs.close()
  conn.close()
  
"""

escape_keys = ("\\", "`", ";")
escape_values = ("\\", "'", ";")

def mksql(string, keys = False, values = False, **kwars):
  """Fills string. %(Ksome_name)s will be subsituted
    with the key-like (think of columns and table names) quoted
    value of keys["some_name"], while
    %(Vsomething)s will be substituted by the value-like quoted
    value of values["something"] or keys["something"] if values
    is not defined.

    Note that this function raises the usual KeyError when
    a value requested is not found in the dict."""

  if not keys:
    keys = kwars
  if not values:
    values = keys
  d = {}
  for k, v in keys.items():
    d["k" + k] = to_key(str(v))
    d["K" + k] = "`" + to_key(str(v)) + "`"
    # R is for "raw". Please use this with extreme
    # caution, since raw data is not preprocessed at all.
    # Whatever you take as raw data should
    # - not depend on user accesiable input
    # or
    # - be checked very very carefully!
    d["R" + k] = str(v)

  for k, v in values.items():
    d["v" + k] = to_value(str(v))
    d["V" + k] = "'" + to_value(str(v)) + "'"
  
  return string % d

def to_key(v):
  """Converts an unstrusted sql-key into a save one.
    Note that this DOES NOT add the ` - signs on the left
    and the right, but please dont provide them in the paramter,
    because there they will be quoted.
    You'll have to add them yourself later!
    This function will be called by mksql."""
  for r in escape_keys:
    v = v.replace(r, "\\" + r)
  return v

def to_value(v):
  """Converts an unstrusted sql-value into a save one.
    Note that this DOES NOT add the ' - signs on the left
    and the right, but please dont provide them in the paramter,
    because there they will be quoted.
    You'll have to add them yourself later!
    This function will be called by mksql."""
  for r in escape_values:
    v = v.replace(r, "\\" + r)
  return v

if __name__ == "__main__":
  # short mixed form:
  # sql-string values and keys via keywords
  print mksql("SELECT * FROM %(Ktab)s WHERE %(Kkey)s=%(Vval)s",
      tab="tabelle", key="schlüssel", val="Wert")

  # long mixed form:
  # sql-string, values and keys in one dict
  print mksql("SELECT * FROM %(Ktab)s WHERE %(Kkey)s=%(Vval)s",
      {
        "tab": "tabelle",
        "key": "schlüssel",
        "val": "Wert"
      })

  # long splitted form:
  # sql-string, dict of keys, dict of values
  print mksql("SELECT * FROM %(Ktab)s WHERE %(Kkey)s=%(Vval)s",
      {
        "tab": "tabelle",
        "key": "schlüssel",
      },
      {
        "val": "Wert"
      })