Как правило, добавляемые данные не жестко определены в строке SQL-инструкции, а приходят откуда-то извне, например, с помощью ввода пользователя в консоли и т.д. В этом случае
нам надо взять все данные и объединить их в одну строку. На первый взгляд мы могли бы для этого применять встроенные возможности языка C, например, различные функции для работы строками.
К примеру, возьмем встроенную функцию
#include <stdio.h> #include <sqlite3.h> int main() { sqlite3 *db; // указатель на базу данных char *err_msg = 0; int rc = sqlite3_open("test.db", &db); if (rc != SQLITE_OK) { sqlite3_close(db); return 1; } // данные для добавления char name[20] = "Bill"; int age = 21; // строка, куда будет помещаться код sql char sql[200]; // создаем форматированную строку с кодом SQL sprintf(sql, "INSERT INTO people (name, age) VALUES ('%s', %d)", name, age); rc = sqlite3_exec(db, sql, 0, 0, &err_msg); if (rc != SQLITE_OK ) { printf("SQL error: %s\n", err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); printf("data inserted\n"); return 0; }
Здесь добавляемые данные представляют пременные name и age. Для хранения sql-кода определяем символьный массив sql
, который должен быть достаточно большим, чтобы вместить в себя код.
Затем с помощью функции sprintf()
форматируем строку с инструкцией INSERT, вставляя в нее значения переменных name и age.
Этот способ прекрасно работает. Но посмотрим, что будет, если переменные name и age будут иметь следующие значения:
char name[40] = "', 1);DROP TABLE IF EXISTS people;--"; int age = 21;
В этом случае у нас сформируется следующий код SQL
INSERT INTO people (name, age) VALUES ('', 1);DROP TABLE IF EXISTS people;--', 21)
В итоге вместо добавления данных мы получим удаление всей таблицы. И чтобы решить данную проблему, нам надо использовать параметризированные запросы.
Для создания параметризированных запросов предназначена структура sqlite3_stmt. Объект этой структуры представляет SQL-выражение, которое компилируется в бинарную форму и готово к выполнению.
Работа c sqlite3_stmt
проходит ряд этапов:
Создание объекта с помощью функции sqlite3_prepare_v2()
int sqlite3_prepare_v2( sqlite3 *db, /* указатель на базу данных */ const char *zSql, /* SQL-выражение (в кодировке UTF-8) */ int nByte, /* максимальный размер в байтах строки zSql*/ sqlite3_stmt **ppStmt, /* указатель на компилируемое выражение sqlite3_stmt */ const char **pzTail /* указатель на неиспользуемую часть zSql */ );
Привязка параметров к выражению SQL с помощью функций sqlite3_bind_*()
Выполнение SQL посредством функции sqlite3_step()
Удаление объекта с помощью функции sqlite3_finalize()
Рассмотрим на примере добавления данных:
#include <stdio.h> #include <sqlite3.h> int main() { sqlite3 *db; // указатель на базу данных sqlite3_stmt *res; // компилируемое выражение int rc = sqlite3_open("test.db", &db); if (rc != SQLITE_OK) { sqlite3_close(db); return 1; } // добавляем данные char* name = "Tom"; int age = 22; // выполняемый код SQL char *sql = "INSERT INTO people (name, age) VALUES (?, ?)"; // готовим выражение rc = sqlite3_prepare_v2(db, sql, -1, &res, 0); if (rc == SQLITE_OK) { // привязываем параметры sqlite3_bind_text(res, 1, name, -1, SQLITE_STATIC); sqlite3_bind_int(res, 2, age); // выполняем выражение int step = sqlite3_step(res); // если выражение успешно выполнено if (step == SQLITE_DONE) { printf("data inserted\n"); } } else { // выводим сообщение об ошибке fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); } // удаляем скомпилированное выражение sqlite3_finalize(res); sqlite3_close(db); return 0; }
Сначала определяем указатель на объект sqlite3_stmt
sqlite3_stmt *res;
Для добавления определяем следующий код SQL:
char *sql = "INSERT INTO people (name, age) VALUES (?, ?)";
Вместо знаков вопроса будут вставлять значения переменных name и age.
Далее создаем выражение с помощью функции sqlite3_prepare_v2
rc = sqlite3_prepare_v2(db, sql, -1, &res, 0);
Здесь передаем указатель на базу данных и код sql. Третий параметр указывает на максимальный размер в байтах выражения SQL. В данном случае передается число -1, что указывает, что окончанием строки с кодом SQL будет нулевой байт (по сути конец строки). Четвертый параметр - компилируемое выражение sqlite3_stmt. Пятый параметр не важен, поэтому , просто указываем 0.
При успешном выполнении этой функции (если она возвращает SQLITE_OK
) должен быть создан объект sqlite3_stmt
. В случае ошибки же переменная
res
будет равна NULL.
Если все прошло успешно, привязываем параметры к компилируемому выражению:
if (rc == SQLITE_OK) { // привязываем параметры sqlite3_bind_text(res, 1, name, -1, SQLITE_STATIC); sqlite3_bind_int(res, 2, age);
Для привязки параметров определенного типа применяются функции sqlite3_bind-тип_данных
. Доступно несколько таких функций:
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_blob64(sqlite3_stmt*, int, const void*, sqlite3_uint64, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*)); int sqlite3_bind_text64(sqlite3_stmt*, int, const char*, sqlite3_uint64, void(*)(void*), unsigned char encoding); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void(*)(void*)); int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n); int sqlite3_bind_zeroblob64(sqlite3_stmt*, int, sqlite3_uint64);
У всех функций первый параметр представляет компилируемое выражение sqlite3_stmt
, второй параметр - индекс параметра в инструкции SQL (номер знака вопроса), а третий параметр -
привязываемое значение.
Для функций sqlite3_bind_blob
(привязка массива байт) и sqlite3_bind_text
(привязка текста) также надо указать четвертый параметр - размер данных в байтах. Для строки в этом случае
можно указать число -1, тогда привязывается строка вплоть до нулевого байта.
Пятый параметр устанавливает функцию, которая управляет удалением объекта. Здесь мы можем использовать специальную константу SQLITE_STATIC, которая говорит, что удалением привязанного значения будет управлять само приложение.
Так, в примере выше в вызове
sqlite3_bind_text(res, 1, name, -1, SQLITE_STATIC);
привязываем к первому параметру значение переменной name, при этом размер привязанных данных - вплоть до конца строки, а управлять удалением объекта будет само приложение.
После привязки выполняем скомпилированное выражение
int step = sqlite3_step(res);
Если эта функция возвратила числовой код SQLITE_DONE
, значит выполнение прошло успешно.
В конце удаляем объект sqlite3_stmt
sqlite3_finalize(res);
Выше был рассмоторен пример выполнения выражения SQL, которое выполняло операцию добавления. И то же самое можно применять и для операций обновления и удаления. Но если нам
надо получить данные из БД с помощью команды SELECT
, то процесс получения будет иметь некоторые особенности. В частности, после успешного выполнения функции
int step = sqlite3_step(res);
переменная step будет равна SQLITE_ROW, а переменная res
будет содержать данные извлеченной строки из бд.
Чтобы получить извлеченные данные из res
, надо вызвать одну из функций sqlite3_column_тип-данных
:
sqlite3_column_blob
: возвращает BLOB
sqlite3_column_double
: возвращает число типа REAL
sqlite3_column_int
: возвращает 32-битное число INTEGER
sqlite3_column_int64
: возвращает 64-битное число INTEGER
sqlite3_column_text
: возвращает строку TEXT в кодировке UTF-8
sqlite3_column_text16
: возвращает строку TEXT в кодировке UTF-16
sqlite3_column_value
: возвращает объект sqlite3_value
В качестве первого параметра эти функции принимают объект sqlite3_stmt
, а в качестве второго - номер столбца.
Например, выберем из таблицы пользователей, у которых возраст больше определенного:
#include <stdio.h> #include <sqlite3.h> int main() { sqlite3 *db; // указатель на базу данных sqlite3_stmt *res; // указатель на скомпилированное выражение int rc = sqlite3_open("test.db", &db); if (rc != SQLITE_OK) { sqlite3_close(db); return 1; } int age = 30; rc = sqlite3_prepare_v2(db, sql, -1, &res, 0); if (rc == SQLITE_OK) { // привязываем параметры sqlite3_bind_int(res, 1, age); // перебираем все строки из результата while (sqlite3_step(res) == SQLITE_ROW) { printf("Id: %d\t", sqlite3_column_int(res, 0)); printf("Name: %s\t", sqlite3_column_text(res, 1)); printf("Age: %d\n", sqlite3_column_int(res, 2)); } } // удаляем скомпилированное выражение sqlite3_finalize(res); sqlite3_close(db); return 0; }
Здесь в цикле while выполняем функцию sqlite3_step(res)
, пока она возвращает SQLITE_ROW
(то есть пока есть строки)
while (sqlite3_step(res) == SQLITE_ROW)
Столбцы по умолчанию возвращаются в том порядке, в котором они определены в таблицы. Поэтому первый столбец - столбец id, который имеет тип Integer. Поэтому для получения его значения используем
функцию sqlite3_column_int()
sqlite3_column_int(res, 0)