Как правило, добавляемые данные не жестко определены в строке 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)