→ Типы SQL-запросов в приложениях

| No TrackBacks
В этом посте описаны различные виды SQL-выражений, которые можно встретить в приложениях, работающих с базами данных: embedded, static, dynamic, prepared, parameterized (bind variables) SQL. Данные типы не являются непересекающимися множествами. Например, embedded является способом записи, а bind variables используется как в статических (неявно), так и в динамических (явно) запросах.
1. Embedded SQL
Это способ записи SQL-выражений прямо в тексте программы. Выражения SQL обрамляются специальными тегами, которые препроцессором (прекомпилятором) преобразуются в конструкции языка программирования. Наиболее известными являются DB2-прекомпилятор (для традиционных языков программирования для мэйнфреймов) и SQLJ.

1) Пример для DB2 и C++:

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL BEGIN DECLARE SECTION;
char Name[128];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT * FROM Q.STAFF WHERE NAME = :Name;
EXEC SQL SELECT NAME :Name FROM Q.STAFF;
if (sqlca.sqlcode != 0L) {
...
}
В первых двух строках подключаются DB2-структуры, используемые для контроля результатов выполнения SQL-выражений. В 3-й строке объявляется маркер начала блока хост-переменных (host variables)- переменных, которые можно использовать внутри SQL-запросов. 5-я строка заканчивает блок хост-переменных. В 6-й строке представлен запрос с хост-переменной, значение которой используется. Запрос в 7-й строке демонстрирует получение данных в хост-переменную. sqlca.sqlcode содержит код завершения предыдущего SQL-выражения. В последних версиях мэйнфрейм-компиляторов DB2-прекомпилятор может быть вызван неявно путем указания опции SQL прямо компилятору.

2) Пример для Java с SQLJ и Oracle:

String Name;
#sql {
    SELECT NAME, INTO :OUT Name
        FROM STAFF
};
2. Static SQL
Использование статических запросов позволяет снизить временную стоимость обращений к базе данных. Это достигается за счет введения дополнительного шага BIND, выполняемого один раз при установке приложения на указанную БД.
После обработки файла с embedded SQL DB2-прекомпилятор создает особый DBRM (DataBase Request Module) файл, который содержит описание SQL-выражений во внутреннем формате. Для того, чтобы приложение могло взаимодействовать с БД, необходимо связать (BIND) SQL-выражения с БД, то есть, трансформировать DBRM в некоторую описательную сущность в БД, называемую планом (PLAN). Из каждого исходного модуля приложения, взаимодействующего с БД, получается один DBRM. Соответственно, один план может содержать описания для многих модулей. Обыкновенно в этом случае план составляют из пакетов (PACKAGE), а каждый пакет из одной DBRM. Именно пакеты содержат информацию, необходимую БД для быстрого выполнения запроса. Заполнение этой информации будет происходить на этапе BIND еще перед запуском приложения. На этом же шаге происходит валидация запроса - проверка соответствия колонок, типов хост-переменных и т.д. фактическим объектам в БД.
В примере для DB2 из п.1 оба запроса являются статическими - полная информация о запросе доступна исходя из вида SQL-выражения, будет сохранена DB2-прекомпилятором в DBRM, и впоследствии сохранена в виде пакета в БД при выполнении связывания (BIND). Если запрос размером в мегабайт, то BIND (построение оптимальной схемы выполнения запроса) может занять продолжительное время - до нескольких десятков минут, в зависимости от производительности сервера, но это время тратится один раз, и при выполнении большой запрос отрабатывает мгновенно.
Также объединение static + embedded SQL присутствует в PL/SQL, и, судя по всему, в хранимых процедурах на Transact-SQL.

3. Dynamic SQL
Динамические запросы применяют в тех случаях, когда неизвестен вид выполняемого запроса (например, в общих клиентах для БД), а также в скриптовых языках, когда о ни о какой компиляции / прекомпиляции не может быть речи. SQL-выражение указывается как строка-значение некоторой переменной. Перед выполнением данного выражение СУБД должна его разобрать и составить план. БД поддерживают осуществление такой подготовки явно (шаг prepare statement) или неявно. Запросы, которым prepare делается явно, называют prepared SQL. Это позволяет один раз подготовить план, а затем много раз быстро выполнять запрос.

1) Prepared SQL для DB2:

EXEC SQL BEGIN DECLARE SECTION;
struct {
    short Length;
    char Statement[1024];
} PreparedStatement;
char Name[128];
EXEC SQL END DECLARE SECTION;
char *Query = "SELECT NAME FROM Q.STAFF";
PreparedStatement.Length = strlen(Query);
strcpy(PreparedStatement.Statement, Query);
EXEC SQL DECLARE SELECT_CURSOR FOR SELECT_STMT;
EXEC SQL PREPARE SELECT_STMT FROM :PreparedStatement;
EXEC SQL OPEN SELECT_CURSOR;
EXEC SQL FETCH SELECT_CURSOR INTO :Name;
EXEC SQL CLOSE SELECT_CURSOR;

2) Non-Prepared SQL для DB2:

EXEC SQL BEGIN DECLARE SECTION;
struct {
    short Length;
    char Statement[1024];
} ExecutedStatement;
EXEC SQL END DECLARE SECTION;
char *Query = "INSERT INTO Q.STAFF (NAME) VALUES ('MEDVED')";
ExecutedStatement.Length = strlen(Query);
strcpy(ExecutedStatement.Statement, Query);
EXEC SQL EXECUTE IMMEDIATE :ExecutedStatement;

DB2 не поддерживает SELECT-выражения для Non-Prepared SQL.

3) Prepared SQL для MySQL:

char *Query = "SELECT NAME FROM STAFF";
mysql_stmt_prepare(stmt, Query, strlen(Query));
mysql_stmt_execute(stmt);

4) Non-Prepared SQL для MySQL:

char *Query = "SELECT NAME FROM STAFF";
mysql_query(stmt, Query);
4. Parameterized SQL
Это способ записи prepared SQL с указанием мест для хост-переменных. Все статические SQL, в которых используются хост-переменные, являются параметризованными - явно отделены SQL-выражения от переменных, по-другому статический SQL не записать. Однако, термин "Parameterized SQL" как правило применяется к динамическим запросам. В строку запроса вставляются спецсимволы '?', и СУБД, при составлении плана, понимает, что при выполнении запроса нужно ожидать некоторое количество хост-переменных определенных типов. Так как план запроса составляется по "голому" запросу без значений переменных, то невозможно изменение запроса путем проведения SQLINJ.

1) Пример для DB2 (на основе примера 1 из п.3)

char *Query = "SELECT NAME FROM Q.STAFF WHERE ID = ?";
...
EXEC SQL OPEN SELECT_CURSOR USING :Identifier;

2) Пример для MySQL + JDBC:

PreparedStatement ps = conn.prepareStatement(
    "SELECT NAME FROM STAFF WHERE ID = ?");
ps.setString(1, Identifier);
ps.executeQuery();

3) Пример для MySQL + PHP (самой распространенной и проблемной связки):

$link = mysqli_connect("localhost", "root", "", "mysql");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}                                                                                   
if ($stmt = mysqli_prepare($link, 
        "SELECT User, Password FROM User WHERE User = ?")) {
    $user = "root";
    mysqli_stmt_bind_param($stmt, "s", $user);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $res_user, $res_pass);
    mysqli_stmt_fetch($stmt);
    printf ("'%s' => '%s'\n", $res_user, $res_pass);
}
else {
    printf("Prepare failed: %s\n", mysqli_error($link));
}
mysqli_close($link);

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

No TrackBacks

TrackBack URL: http://smokedchicken.org/m/mt-tb.cgi/37

About this Entry

This page contains a single entry by Павел Збицкий published on July 6, 2010 12:27 AM.

Получение шелла через SQLite3 в веб-директории PHP was the previous entry in this blog.

Уязвимость "Hole 196" в WPA2 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.