Лучшие практики работы с PreparedStatements; когда можно и когда нельзя

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

Я читал о том, когда их использовать, а когда не использовать, но ни один из примеров не говорит о наилучшей практике их использования.

Я пытаюсь выяснить, для каких вызовов базы данных я должен их использовать, а для каких нет.

Например, веб-сайт MySQL упоминает об этом в разделе «Когда использовать подготовленные операторы» на следующей странице Подготовленные операторы-MySQL


mysql java prepared-statement
person TheJediCowboy    schedule 02.08.2010    source источник
comment
Мне интересно узнать статьи/документы, в которых указано, когда не использовать подготовленные заявления. Честно говоря, если вся ваша логика доступа к базе данных, то есть SQL-запросы, выполняется на Java, нет большого смысла избегать подготовленных операторов.   -  person Vineet Reynolds    schedule 02.08.2010
comment
Я включил статью в редактирование выше.   -  person TheJediCowboy    schedule 02.08.2010

Ответы (3)


arrow_upward
6
arrow_downward

Общее эмпирическое правило при принятии решения о том, использовать PreparedStatement или нет, таково:

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

Судя по статье, на которую вы ссылались, список причин, по которым я считаю, что подготовленные операторы менее полезны, чем обычные запросы или хранимые процедуры, таков:

  • Одноразовые запросы. Если ваше приложение делает один запрос к базе данных, и это делается нечасто по сравнению с другими запросами, в этом случае может не иметь смысла использовать подготовленный оператор. Обоснование состоит в том, что подготовленный оператор должен быть сначала скомпилирован, а «скомпилированная» форма оператора кэшируется для последующего использования. Для запросов, которые выполняются нечасто, компиляция является накладной. Но все же предпочтительнее использовать подготовленные операторы, чтобы избежать проблем с внедрением SQL.
  • Операции с интенсивным использованием данных. Иногда подготовленные операторы не так эффективны, как хранимые процедуры, особенно когда в одной и той же транзакции необходимо выполнить последовательность операций. Когда у вас есть бизнес-процесс, который требует выполнения нескольких операций выбора, обновления и удаления для множества таблиц, хранимые процедуры часто лучше, чем набор подготовленных операторов, выполняемых один за другим. Это снижение производительности может стать серьезным, поскольку для выполнения нескольких операторов выполняется несколько сетевых подключений, что значительно снижается при вызове хранимой процедуры. Этот эффект более заметен при пакетной обработке запросов, когда несколько объектов создаются и уничтожаются за короткий промежуток времени. Это часто вызывает споры между администраторами баз данных и разработчиками приложений, поскольку это крайний случай; Администраторы баз данных считают, что группирование операций лучше выполнять с помощью SP, в то время как разработчики приложений считают, что с этим может справиться PreparedStatements (обычно лучше иметь всю логику на одном уровне). В конечном итоге все сводится к заявке на то, является ли использование SP преимуществом или нет.
  • Поддержка собственных операций и типов баз данных. Это может быть неприемлемо для MySQL, но в целом стандарт JDBC не поддерживает все операции, поддерживаемые базой данных, и все поддерживаемые базой данных SQL/собственные/пользовательские типы. Это более заметно в базе данных Oracle (и, возможно, в IBM DB2?), где программисты могут создавать свои собственные типы, которые требуют написания пользовательского кода Java, поскольку стандарт JDBC не поддерживает определяемые пользователем типы в базе данных. Точно так же другие операции в базе данных не должны поддерживаться (как указано в документе MySQL) - нельзя создавать пользователей (выполнять CREATE USER), изменять привилегии пользователя (выполнять операции GRANT) и т. д. с помощью подготовленного заявления. Хранимые процедуры лучше подходят для этой задачи, поскольку они будут иметь прямой или косвенный доступ к собственному набору операций базы данных.
person Vineet Reynolds    schedule 02.08.2010
comment
даже одноразовые запросы компилируются, но когда вы их запускаете, а не когда вы их готовите. Едва ли есть случаи, когда вы не должны использовать подготовленные операторы. Особенно для безопасности! - person Patrick Cornelissen; 02.08.2010
comment
@ Патрик, нет сомнений, что компиляция происходит, когда также используются обычные объекты Statement. Использование объектов PreparedStatement приведет к предварительной компиляции SQL-запросов (при условии, что драйвер базы данных поддерживает это, что обычно случается почти со всеми драйверами, встречающимися в повседневной жизни). Разница между ними заключается в том, что предварительно скомпилированные запросы имеют заполнители для вставки пользовательского ввода, при этом вставка выполняется драйвером совместно с базой данных. Таким образом, использование объектов Statement — это не то же самое, что и PreparedStatement, хотя и требуется компиляция. - person Vineet Reynolds; 02.08.2010
comment
Что касается случаев, когда PreparedStatement не может быть использован, и подразумеваемой внутренней безопасности, PreparedStatements не может гарантировать безопасность, когда у вас есть инструкция, такая как SELECT colA FROM tabB, где colA является пользовательским вводом; просто нельзя иметь PreparedStatement, объявленный как SELECT ? ИЗ вкладки Б. Прекомпиляция имеет свои ограничения. - person Vineet Reynolds; 02.08.2010
comment
Да, это тот случай, когда подготовленные операторы не работают так хорошо. Хотя вы можете создавать подготовленные операторы, которые строятся динамически. Но вы должны очень строго проверять данные, которые используются для построения оператора, чтобы избежать SQL-инъекций или других ошибок безопасности. - person Patrick Cornelissen; 02.08.2010

arrow_upward
2
arrow_downward

Чтобы предотвратить SQL-инъекцию, лучше использовать подготовленные операторы в Java.

Для получения дополнительной информации: SQL-инъекции с подготовленными операторами?

person Upul Bandara    schedule 02.08.2010

arrow_upward
2
arrow_downward

У PreparedStatements есть два основных применения:

  1. Предотвращение атак путем внедрения кода SQL. Это в основном означает автоматическую очистку входных данных из внешних источников (веб-браузер является внешним!), которые будут сохранены в базе данных.
  2. Пакетная обработка. Если у вас есть много данных, которые нужно ввести/изменить/удалить из базы данных одновременно, для этого можно использовать PreparedStatement. В этом случае PreparedStatement оптимизирует большую часть накладных расходов на такие операции и позволяет вам писать быстрый пакетный код базы данных.

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

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

person Esko    schedule 02.08.2010