Zapytania parametryczne MySQL


80

Mam problemy z używaniem modułu MySQLdb do wstawiania informacji do mojej bazy danych. Muszę wstawić 6 zmiennych do tabeli.

cursor.execute ("""
    INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
    VALUES
        (var1, var2, var3, var4, var5, var6)

""")

Czy ktoś może mi tu pomóc ze składnią?

Odpowiedzi:


262

Uważaj na używanie interpolacji ciągów w zapytaniach SQL, ponieważ nie spowoduje to poprawnej zmiany parametrów wejściowych i pozostawi Twoją aplikację otwartą na luki w iniekcji SQL. Różnica może wydawać się banalna, ale w rzeczywistości jest ogromna .

Niepoprawnie (z problemami bezpieczeństwa)

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

Prawidłowo (ze ucieczką)

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

To pogłębia zamieszanie, że modyfikatory używane do wiązania parametrów w instrukcji SQL różnią się w zależności od różnych implementacji DB API oraz że biblioteka klienta mysql używa printfskładni stylu zamiast powszechnie akceptowanego „?” marker (używany np. python-sqlite).


3
@Specto IMO zawsze warto trzymać się właściwych i bezpiecznych sposobów implementacji. Tworzy właściwe nawyki i dobrą kulturę programowania. Nikt również nie wie, jak Twój kod będzie używany w przyszłości; ktoś może go później użyć w innym systemie lub witrynie.
Roman Podlinov

@BryanHunt Możesz włączyć korzystanie z? gdzieś z argumentem, ale jest odradzany, ponieważ nie mówi zbyt wiele o tym, który argument prowadzi. (To samo można oczywiście powiedzieć o% s, co jest odradzane z tego samego powodu.) Więcej informacji tutaj: python.org/dev/peps/pep-0249/#paramstyle
kqr

1
Wychodząc z php / pdo byłem mega zdezorientowany, jeśli chodzi o znacznik printfstylu %s, i byłem trochę przerażony, że piszę wrażliwe zapytania. Dzięki za wyjaśnienie tego problemu! :)
Darragh Enright

18
Gdy jest to pojedynczy parametr pamiętaj, aby zachować przecinek: c.execute("SELECT * FROM foo WHERE bar = %s", (param1,))
Marius Lian

1
W kontekście parametrów wykonania kursora (i dlaczego% s jest nadal potrzebne), odniesienie API dla kursora MySQLdb znajduje się pod adresem mysql-python.sourceforge.net/MySQLdb-1.2.2/public/ ...
RedBarron

50

Masz kilka dostępnych opcji. Będziesz chciał poczuć się komfortowo z iterpolacją ciągów w Pythonie. Którego terminu możesz odnieść w przyszłości, jeśli chcesz poznać takie rzeczy.

Lepsze w przypadku zapytań:

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

Biorąc pod uwagę, że prawdopodobnie masz już wszystkie swoje dane w obiekcie lub słowniku, drugi format będzie Ci bardziej odpowiadał. Do bani jest też liczenie wystąpień „% s” w ciągu znaków, kiedy musisz wrócić i zaktualizować tę metodę w ciągu roku :)


2
Wygląda na to, że podejście słownikowe działa lepiej w przypadku, gdy dana zmienna powiązania musi być używana w więcej niż jednym miejscu w instrukcji SQL. W podejściu pozycyjnym musimy przekazywać zmienną tyle razy, ile jest do niej odwołań, co nie jest zbyt pożądane.
codeforester

15

Połączone dokumenty zawierają następujący przykład:

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

Więc wystarczy dostosować to do własnego kodu - przykład:

cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%s, %s, %s, %s, %s, %s)

        """, (var1, var2, var3, var4, var5, var6))

(Jeśli SongLength jest numeryczna, może być konieczne użycie% d zamiast% s).


1
czy to zadziała, gdy zmienna1 i zmienna2 mają znaki takie jak „lub”.
sheki

3
AFAIK musisz %stam używać bez względu na typ. @sheki: Tak
ThiefMaster

7

Właściwie, nawet jeśli twoja zmienna (SongLength) jest numeryczna, nadal będziesz musiał sformatować ją za pomocą% s, aby poprawnie powiązać parametr. Jeśli spróbujesz użyć% d, pojawi się błąd. Oto mały fragment tego łącza http://mysql-python.sourceforge.net/MySQLdb.html :

Aby wykonać zapytanie, potrzebujesz najpierw kursora, a następnie możesz wykonywać na nim zapytania:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

W tym przykładzie max_price = 5 Dlaczego w takim razie używać% sw ciągu? Ponieważ MySQLdb przekonwertuje ją na literał SQL, którym jest ciąg „5”. Po zakończeniu zapytanie faktycznie powie „… GDZIE cena <5”.


Tak, to jest dziwne, jasne ... myślisz, że format "printf" oznaczałby ... właściwie format printf, a nie tylko używanie% s wszędzie.
fthinker

6

Jako alternatywę dla wybranej odpowiedzi i z tą samą bezpieczną semantyką co Marcela, tutaj jest zwarty sposób użycia słownika Pythona do określenia wartości. Ma tę zaletę, że można go łatwo modyfikować podczas dodawania lub usuwania kolumn do wstawienia:

  meta_cols=('SongName','SongArtist','SongAlbum','SongGenre')
  insert='insert into Songs ({0}) values ({1})'.
        .format(','.join(meta_cols), ','.join( ['%s']*len(meta_cols) ))
  args = [ meta[i] for i in meta_cols ]
  cursor=db.cursor()
  cursor.execute(insert,args)
  db.commit()

Gdzie meta to słownik przechowujący wartości do wstawienia. Aktualizację można przeprowadzić w ten sam sposób:

  meta_cols=('SongName','SongArtist','SongAlbum','SongGenre')
  update='update Songs set {0} where id=%s'.
        .format(','.join([ '{0}=%s'.format(c) for c in meta_cols ]))
  args = [ meta[i] for i in meta_cols ]
  args.append( songid )
  cursor=db.cursor()
  cursor.execute(update,args)
  db.commit()

7
Jestem pod wrażeniem ... udało ci się uczynić kod Pythona nieczytelnym!
Iharob Al Asimi

1

Pierwsze rozwiązanie działa dobrze. Chcę tutaj dodać jeden mały szczegół. Upewnij się, że zmienna, którą próbujesz zastąpić / zaktualizować, musi być typu str. Mój typ mysql jest dziesiętny, ale musiałem ustawić zmienną parametru jako str, aby móc wykonać zapytanie.

temp = "100"
myCursor.execute("UPDATE testDB.UPS SET netAmount = %s WHERE auditSysNum = '42452'",(temp,))
myCursor.execute(var)

0

Oto inny sposób, aby to zrobić. Jest to udokumentowane na oficjalnej stronie MySQL. https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

W duchu używa tej samej mechaniki, co odpowiedź @Trey Stout. Jednak uważam, że ten jest ładniejszy i bardziej czytelny.

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

Aby lepiej zilustrować potrzebę stosowania zmiennych:

Uwaga: zwróć uwagę na ucieczkę.

employee_id = 2
first_name = "Jane"
last_name = "Doe"

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (employee_id, conn.escape_string(first_name), conn.escape_string(last_name), datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.