Как работает параметризация SQL-запроса?

Я чувствую себя немного глупо, спрашивая об этом, так как я, кажется, единственный человек в мире, который этого не понимает, но здесь все равно. Я собираюсь использовать Python в качестве примера. Когда я использую необработанные SQL-запросы (обычно я использую ORM), я использую параметризацию, как этот пример, используя SQLite:

Метод A:

username = "wayne"
query_params = (username)
cursor.execute("SELECT * FROM mytable WHERE user=?", query_params)

Я знаю, что это работает, и я знаю, что это обычно рекомендуемый способ сделать это. Уязвимость для SQL-инъекций для одного и того же объекта будет выглядеть примерно так:

Метод B:

username = "wayne"
cursor.execute("SELECT * FROM mytable WHERE user='%s'" % username)

Насколько я могу судить, я понимаю SQL-инъекцию, как описано в этой статье в Википедии. Мой вопрос просто: как метод A действительно отличается от метода B? Почему конечный результат метода A не совпадает с методом B? Я предполагаю, что метод cursor.execute() (часть спецификации DB-API Python) заботится о правильном экранировании и проверке типов ввода, но это никогда не упоминается нигде. Это все, что параметризация в этом контексте? Для меня, когда мы говорим "параметризация", все это означает "замену строк", например,% -форматирование. Это неверно?

Ответ 1

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

SELECT * FROM mytable WHERE user='wayne'

Если вы используете параметр ?, то механизм SQL видит запрос, который выглядит как

SELECT * FROM mytable WHERE user=<some value>

Это означает, что перед тем, как он даже увидит строку "wayne", она может полностью проанализировать запрос и понять, как правило, то, что делает запрос. Он привязывает "wayne" к собственному представлению запроса, а не к строке SQL, которая описывает запрос. Таким образом, SQL-инъекция невозможна, поскольку мы уже прошли этап SQL процесса.

(Вышеизложенное обобщено, но оно более или менее передает идею.)

Ответ 2

когда вы выполняете замену текста (например, ваш метод B), вы должны опасаться кавычек и т.д., потому что то, что получит сервер, представляет собой единый фрагмент текста, и он должен определить, где заканчивается значение.

С параметризованными операторами OTOH сервер БД получает оператор как есть, без параметра. Значение отправляется на сервер как разные части данных, используя простой двоичный безопасный протокол. Поэтому вашей программе не нужно ставить кавычки вокруг значения, и, конечно, не имеет значения, были ли уже кавычки в самом значении.

Аналогия относится к исходному и скомпилированному коду: в вашем методе B вы создаете исходный код процедуры, поэтому вам обязательно нужно строго следовать синтаксису языка. С помощью метода A вы сначала создаете и компилируете процедуру, затем (сразу после, в вашем примере) вы вызываете эту процедуру с вашим значением в качестве параметра. И, конечно, значения в памяти не подпадают под ограничения синтаксиса.

гул... на самом деле это не была аналогия, это действительно то, что происходит под капотом (примерно).

Ответ 3

Использование параметризованных запросов - хороший способ выполнить задачу для экранирования и предотвращения инъекций в клиентскую библиотеку БД. Он выполнит побег, прежде чем заменяет строку "?". Это делается в клиентской библиотеке до сервера БД.

Если у вас запущен MySQL, включите SQL-журнал и попробуйте несколько параметризованных запросов, и вы увидите, что сервер MySQL получает полностью замещенные запросы без "?" в нем, но клиентская библиотека MySQL уже избегала каких-либо кавычек в вашем "параметре" для вас.

Если вы используете метод B только с заменой строки, "s не автоматически экранируется.

Синергетически, с MySQL, вы можете подготовить параметризованный запрос раньше времени, а затем использовать подготовленный оператор несколько позже. Когда вы готовите запрос, MySQL анализирует его и возвращает вам подготовленный оператор - некоторое анализируемое представление MySQL понимает. Каждый раз, когда вы используете подготовленный оператор, вы не только защищаетесь от инъекций, но также избегаете затрат на разбор запроса снова.

И, если вы действительно хотите быть в безопасности, вы можете изменить свой уровень доступа к БД /ORM, чтобы 1) код веб-сервера мог использовать только подготовленные операторы, и 2) вы можете только готовить заявления до запуска вашего веб-сервера. Затем, даже если ваше веб-приложение взломано (скажем, с помощью переполнения буфера), хакер может использовать только готовые заявления, но не более того. Для этого вам нужно заключить в тюрьму ваше веб-приложение и разрешать доступ к базе данных только через ваш доступ к базе данных/ORM.

Ответ 4

Просто предостережение здесь. Эта? синтаксис будет работать очень хорошо и позволяет избежать встроенных одиночных или двойных кавычек в строках правильно.

Однако я нашел один случай, когда он не работает. У меня есть столбец, который отслеживает строку версии формы "n.n.n", например. "1.2.3" Кажется, что формат вызывает ошибку, потому что он выглядит как реальное число до второго ".". Например:

   rec = (some_value, '1.2.3')
   sql = ''' UPDATE some_table
              SET some_column=?
              WHERE version=? '''
    cur = self.conn.cursor()
    cur.execute(sql, rec)

Ошибка с ошибкой "Неправильное количество добавленных привязок. В текущем операторе используется 1, и есть 2 поставленных."

Это работает нормально:

   vers = '1.2.3'
   rec = (some_value)
   sql = ''' UPDATE some_table
              SET some_column=?
              WHERE version='%s' ''' % (vers)
    cur = self.conn.cursor()
    cur.execute(sql, rec)

Ответ 5

Когда вы отправляете запрос по SQL Server, он сначала проверяет кеш процедур. Если он находит somequery EXACTLY равным, то он будет использовать тот же план, а не перекомпилировать запрос, просто заменит заполнители (переменные), но на стороне сервера (db).

проверьте системную таблицу master.dbo.syscacheobjects и выполните некоторые тесты, чтобы вы узнали немного больше об этой теме.