Установка и использование Python DataBase-API для (PostgreSQL , MySQL , SQLite ,SQL Server, Oracle) + protect from SQL injection

Всем привет , в данной статье разберёмся с API баз данных для Python , а также экранирование символов от SQL injection

Адаптеры СУБД

Python описывает требования к API баз данных таким образом, чтобы взаимодействие с ними было максимально унифицированным. Как и все в Python, DBI API является рекомендацией и конкретные адаптеры имеют свою специфику.

Для работы с СУБД необходимо установить библиотеку, содержащую адаптер.

Для SQLite

Ничего делать не нужно, поддержка SQLite встроена в интерпретатор.

Для PostgreSQL

При помощи pip напрямую или через файл requirements.txt необходимо

установить библиотеку psycopg2:

pip install psycopg2

Для MySQL

При помощи pip напрямую или через файл requirements.txt необходимо

установить библиотеку mysql-connector-python:

pip install mysql-connector-python

Для SQL Server

Несмотря на наличие родного адаптера pymssql, он не рекомендуется к использованию. Вместо него рекомендуется модуль pyodbc, который взаимодействует с различными СУБД (не только SQL Server) посредством ODBC.

Вам необходимо иметь ODBC установленным в вашей операционной системе (для MacOS, Linux и прочих Unix необходимо отдельно установить unixODBC).

Затем необходимо установить драйвер ODBC для SQL Server:

(https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-fo

r-sql-server?view=sql-server-2017)

Теперь можно установить адаптер для Python при помощи pip напрямую или

через файл requirements.txt:

pip install pyodbc

Для Oracle

При помощи pip напрямую или через файл requirements.txt необходимо

установить библиотеку cx_Oracle:

pip install cx_Oracle

Подключение к СУБД

Для SQLite

import sqlite3
conn = sqlite3.connect(“path_to_db_file”)
# или для БД в оперативной памяти
conn = sqlite3.connect(“:memory:”)

Для PostgreSQL

import psycopg2
conn = psycopg2.connect(
                        host=”127.0.0.1”,
                        port=5432,
                        database=”mydb”,
                        user=username,
                        password=password
)

Для MySQL

import mysql.connector
conn = mysql.connector.connect(
                               host=”127.0.0.1”,
                               port=3306,
                               database=”mydb”,
                               user=username,
                               password=password
)

Для SQL Server (ODBC)

ODBC использует строку подключения в определенном формате. Первым параметром указывается драйвер, который вы установили ранее.

import pyodbc
connection_str = f”DRIVER={ODBC Driver 17 for SQL
Server};SERVER=127.0.0.1;DATABASE=mydb;UID=${username};PWD=${password}”
conn = pyodbc.connect(connection_str)

Для подробностей о возможных драйверах ODBC и параметрах подключения:

(https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows)

Для Oracle

import cx_Oracle
conn = cx_Oracle.connect(username, password, “127.0.0.1/mydb”)

Закрытие соединения

conn.close()

Чтобы не забыть закрыть соединение, можно использовать блок with:

with sqlite3.connect(...) as conn: # или любой другой connect
# использование conn
# conn закрыт автоматически

Простые запросы

Для выполнения запросов должен быть создан курсор.

cur = conn.cursor()

Курсоры в Python легковесны и могут пересоздаваться необходимое число раз (хоть и допускают повторное использование).

Для выполнения запросов, не возвращающих данные:

cur.execute(“CREATE TABLE Test (id INTEGER PRIMARY KEY)”)

DML и DDL запросы необходимо фиксировать:

conn.commit()

PostgreSQL и Oracle требует закрытия использованного курсора:

cur.close()

Для автоматического закрытия курсора в адаптерах этих СУБД можно воспользоваться блоком with:

with conn.cursor() as cur:
# использование cur
# cur закрыт автоматически

Если запрос возвращает данные, сам вызов метода execute() ничем не отличается:

cur.execute(“SELECT * FROM Users”)

В зависимости от того, как мы хотим получать результат этого запроса, можно воспользоваться следующими вариантами.

1. Получить сразу все строки.

data = cur.fetchall()

В этом случае data будет двумерным массивом, строки которого это строки результат запроса, а столбцы – столбцы результата запроса.

2. Получать строки по одной.

row = cur.fetchone()
while row:
    # поработать с row
    row = cur.fetchone()

3. Воспользоваться циклом for.

for row in cur:
    # поработать с row
    .......

SQL Injection

Рассмотрим следующую программу:

login = input(“login: “)
password = input(“password: “)

with conn.cursor() as cur:
    sql = f”””SELECT id
              FROM Users
              WHERE login=’${login}’ AND password=’${password}’
           “””

cur.execute(sql)
result = cur.fetchall()
if result is None or len(result) == 0:
    print(“Login failed”)
else:
    id = result[0][0]
    print(f”Logged in as user ${id}”)

При вводе обычных логина и пароля все будет верно:

login: vpupkin
password: 123qwerty

Запрос получится следующим:

SELECT id
FROM Users
WHERE login=’vpupkin’ AND password=’123qwerty’;

Если пользователь с таким логином и паролем существует, его id будет возвращен.

Если пользователя с таким логином не существует или пароль не верен, то запрос вернет 0 строк.

Однако злоумышленник может ввести следующее:

login: admin’ --

password: 42

При этом запрос получится следующим (ввод пользователя выделен полужирным):

SELECT id
FROM Users
WHERE login=’admin’ --’ AND password=’42’;

Символы ( -- ) в SQL обозначают комментарий, а это значит, что выполнится на

самом деле следующий запрос:

SELECT id
FROM Users
WHERE login=’admin’;

Таким образом, пароль вообще не будет иметь значение в выборке и злоумышленник сможет войти под логином admin, не зная пароля.

Получилось это у него из-за того, что он особым образом сформировал строку ввода и тем самым изменил исходный запрос.

Любые подстановки значений в текст запроса через манипуляцию строками приводят к этой уязвимости.

Допустим, у вас в приложении поиск товаров по наименованию выполняется следующим запросом:

SELECT *
FROM products
WHERE name LIKE ‘%строка_поиска%’;

Злоумышленник вводит следующее значение в строку поиска:

яблоки’; DROP TABLE products; --

Мы получаем следующий запрос:

SELECT *
FROM products
WHERE name LIKE ‘%яблоки’; DROP TABLE products; --%’;

Таким образом выполнятся два запроса:

SELECT *
FROM products
WHERE name LIKE ‘%яблоки’;
DROP TABLE products;

Среди программистов ходит даже такой мем:

Если запомните этот мемчик , то можете быть уверены , что вы никогда не забудете про экранирование символов от SQL injection ;)

Подготовленные запросы

Python не требует особых действий от пользователя для создания подготовленных запросов вручную с точки зрения оптимизации. Некоторое количество последних запросов кэшируется автоматически, так что часто исполняемые запросы не будут тратить время на разбор каждый раз, как они отправляются. Тем не менее, с точки зрения защиты от SQL инъекций библиотеки предоставляют схожие механизмы.

Для SQLite и SQL Server (ODBC)

Вместо подставляемого параметра в запросе указывается знак ( ? ):

sql = “SELECT id FROM Users WHERE login=? AND password=?”
cur.execute(sql, [login, password])
# дальнейшая работа с курсором

Для PostgreSQL и MySQL

Вместо подставляемого параметра в запросе используются допустимые

format-символы. Для любых типов данных допускается использование ( %s ).

sql = “SELECT id FROM Users WHERE login=%s AND password=%s”
cur.execute(sql, [login, password])
# дальнейшая работа с курсором

Для Oracle

Вместо подставляемого параметра в запросе используется знак двоеточия, за которым следует имя параметра.

При подстановке параметров можно передавать как массив значений (поведение аналогичное поведению в других СУБД, имена параметров будут проигнорированы), так и словарь, из которого будут извлекаться ключи, соответствующие именам параметров.

# вариант с массивом
sql = “SELECT id FROM Users WHERE login=:login AND password=:pass”
cur.execute(sql, [login, password])

# дальнейшая работа с курсором

# вариант со словарем
sql = “SELECT id FROM Users WHERE login=:login AND password=:pass”

data = { # это может быть JSON, полученный от клиентского приложения
        “login”: “vpupkin”,

        “pass”: “123qwerty”
}

cur.execute(sql, data)
# дальнейшая работа с курсором

Транзакции

SQLite и PostgreSQL управляют транзакциями автоматически.

SQLite начинает транзакцию перед первым DML запросом и применяет ее

перед запросом другого типа. Запросы DDL фиксируются автоматически.

PostgreSQL заключает в транзакцию все запросы, выполняемые одним

курсором в пределах блока with:

with conn.cursor() as cur:
# можно выполнять запросы в пределах транзакции
# транзакция применена

При необходимости транзакцию можно применить вручную:

conn.commit()

При возникновении исключительных ситуаций, связанных с СУБД, SQLite и

PostgreSQL автоматически откатывают текущую транзакцию. При необходимости, можно откатить транзакцию вручную:

conn.rollback()

MySQL, SQL Server (ODBC), Oracle требуют ручного управления транзакциями.

В MySQL и ODBC значение поля соединения autocommit выставлено в True,

в Oracle – в False.

Если это значение выставлено в True, каждый запрос представляет собой отдельную транзакцию, которая автоматически применяется по завершении.

Для отмены этого поведения необходимо выставить это поле в False:

conn.autocommit = False

Все действия с последнего подтверждения и отката в этом случае будут заключены в транзакцию. Чтобы ее применить:

conn.commit()

Чтобы откатить транзакцию (обычно в блоке except):

conn.rollback()

Получение значений столбцов по их имени

По умолчанию для получения значений столбцов результата запроса

требуется указать их номера как индекс массива:

print(row[0])

Иногда охота указывать столбцы по их имени:

print(row[“id”])

Разные адаптеры имеют различный подход к этому вопросу.

SQLite:

Задание свойства row_factory у объекта подключения позволяет указать фабричную функцию (или класс), формирующую данные о строке. Можно написать фабричную функцию вручную, но встроенный объект Row позволяет это сделать практически без дополнительных вычислительных затрат:

conn.row_factory = sqlite3.Row
cur = cur.cursor()
cur.execute(“SELECT * FROM Users”)

for row in cur:
    print(row[“id”])

PostgreSQL

При создании курсора можно указать какого типа курсор мы хотим.

Недостаток этого подхода: так нужно делать для каждого создаваемого

курсора:

from psycopg2.extras import DictCursor

.......

cur = conn.cursor(cursor_factory=DictCursor)

cur.execute(“SELECT * FROM Users”)
for row in cur:
    print(row[“id”])

MySQL

При создании курсора можно указать, что мы хотим словарь:

cur = conn.cursor(dictionary=True)
cur.execute(“SELECT * FROM Users”)
for row in cur:
    print(row[“id”])

ODBC не поддерживает такой возможности

Oracle поддерживает возможность указания свойства rowfactory у курсора, Однако не содержит готовых решений для этого и требует указания этого свойства для каждого курсора.

Решение со StackOverflow:

cur = conn.cursor()
cur.rowfactory = lambda *args: dict(zip([d[0] for d in 
curs.description], args))

cur.execute(“SELECT * FROM Users”)

for row in cur:
    print(row[“id”])

#E7TEAM