คำสั่งสอบถามแบบกำหนดพารามิเตอร์

อันตรายจากการประกอบสตริง

name = 'Alice'
# Unsafe pattern - do NOT do this
query = "SELECT * FROM users WHERE name = '" + name + "'"
print(query)

ลักษณะของการแทรกคำสั่ง

name = "x' OR '1'='1"
query = "SELECT * FROM users WHERE name = '" + name + "'"
print(query)
print('The OR clause makes every row match!')

ตัวยึดค่าช่วยแก้ปัญหา

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

ตัวยึดค่าหลายตัว

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

ทูเพิลที่มีค่าเดียว

good = ('Alice',)
bad = ('Alice')
print(type(good), type(bad))

การสกัดกั้นความพยายามแทรกคำสั่ง

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
evil = "x' OR '1'='1"
cur.execute('SELECT * FROM users WHERE name = ?', (evil,))
print('Rows matched:', cur.fetchall())
conn.close()

ตัวยึดค่าแบบมีชื่อ

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (:n, :a)', {'n': 'Carol', 'a': 40})
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

พารามิเตอร์ใน WHERE

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
cur.execute('SELECT name FROM users WHERE age > ?', (28,))
print(cur.fetchall())
conn.close()

executemany

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
rows = [('Alice',), ('Bob',), ('Carol',)]
cur.executemany('INSERT INTO users (name) VALUES (?)', rows)
cur.execute('SELECT COUNT(*) FROM users')
print(cur.fetchone())
conn.close()

ตัวยึดค่าใช้แทนตัวระบุไม่ได้

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
allowed = {'name', 'id'}
col = 'name'
if col in allowed:
    cur.execute('SELECT ' + col + ' FROM users')
    print('Safe column query ran')
conn.close()

ฟังก์ชันเพิ่มข้อมูลที่นำกลับมาใช้ใหม่ได้

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')

def add_user(cursor, name):
    cursor.execute('INSERT INTO users (name) VALUES (?)', (name,))

add_user(cur, 'Dave')
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

ตรวจสอบความเข้าใจ

สรุปทบทวน