From 03f8c415ab676732566773004524e2f5f01b7a4f Mon Sep 17 00:00:00 2001
From: Tom Willemse
Date: Sat, 14 Sep 2013 13:00:28 +0200
Subject: Use SQL prepared statement for insertion

When creating a new manga with a name containing a quote (') it would
fail. No message was shown to indicate it had failed and the manga was
not added.

This was caused by not quoting the quote in the SQL. All SQL queries
should use prepared statements in order to make no mistakes quoting
quotes and other things.
---
 src/data.c | 78 +++++++++++++++++++++++++++++++++++++-------------------------
 1 file changed, 47 insertions(+), 31 deletions(-)

diff --git a/src/data.c b/src/data.c
index b183e29..c1b912c 100644
--- a/src/data.c
+++ b/src/data.c
@@ -28,22 +28,26 @@
 
 static gboolean check_and_create_database(gchar*);
 static gint create_new_database(const gchar*);
-static gboolean execute_non_query(const char *sql);
+static gboolean execute_non_query(const char *sql,
+                                  int (*bind)(sqlite3_stmt*));
+static sqlite3 *get_database(void);
 static GList *get_manga_for_query(const gchar*);
 static Manga *get_manga_from_statement(sqlite3_stmt*);
 
 gboolean
 data_add_manga(const gchar *name, gint total_qty)
 {
-    gchar *sql =
-        g_strdup_printf(" INSERT INTO manga (name, current_qty, "
-                        "total_qty) VALUES ('%s', 0, %d)",
-                        name, total_qty);
-    gboolean ret = execute_non_query(sql);
-
-    g_free(sql);
+    gboolean result = FALSE;
+    char sql[] = "INSERT INTO manga (name, current_qty, total_qty) "
+        "VALUES (?, 0, ?)";
+
+    int bind_variables(sqlite3_stmt *stmt) {
+        return (sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC)
+                == SQLITE_OK)
+            && (sqlite3_bind_int(stmt, 2, total_qty) == SQLITE_OK);
+    }
 
-    return ret;
+    return execute_non_query(sql, bind_variables);
 }
 
 gboolean
@@ -53,7 +57,7 @@ data_add_to_manga(gint manga_id, gint count)
         g_strdup_printf(" UPDATE manga "
                         " SET current_qty = current_qty + %d "
                         " WHERE id = %d", count, manga_id);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
@@ -66,7 +70,7 @@ data_add_volume_to_manga(gint manga_id, gint volume)
     char *sql = g_strdup_printf(" INSERT INTO volume "
                                 " VALUES (%d, %d, 0) ",
                                 manga_id, volume);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
@@ -78,14 +82,14 @@ data_delete_manga(gint manga_id)
 {
     char *sql = g_strdup_printf("DELETE FROM volume "
                                 "WHERE manga_id = %d", manga_id);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
     if (ret) {
         sql = g_strdup_printf("DELETE FROM manga "
                               "WHERE id = %d", manga_id);
-        ret = execute_non_query(sql);
+        ret = execute_non_query(sql, NULL);
         g_free(sql);
     }
 
@@ -250,7 +254,7 @@ data_mark_volume_read(int read, gint manga_id, gint volume)
                                  " WHERE  manga_id = %d "
                                  " AND    id = %d ",
                                  read, manga_id, volume);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
@@ -263,7 +267,7 @@ data_remove_volume_from_manga(gint manga_id, gint volume)
     char *sql = g_strdup_printf(" DELETE FROM volume "
                                 " WHERE manga_id = %d "
                                 " AND id = %d ", manga_id, volume);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
@@ -280,7 +284,7 @@ data_update_manga(gint manga_id, const gchar *name, gint total_qty)
                         "       current_qty = MIN(current_qty, %d) "
                         "WHERE id = %d", name, total_qty, total_qty,
                         manga_id);
-    gboolean ret = execute_non_query(sql);
+    gboolean ret = execute_non_query(sql, NULL);
 
     g_free(sql);
 
@@ -289,7 +293,7 @@ data_update_manga(gint manga_id, const gchar *name, gint total_qty)
                               "WHERE manga_id = %d "
                               "AND id > %d",
                               manga_id, total_qty);
-        ret = execute_non_query(sql);
+        ret = execute_non_query(sql, NULL);
         g_free(sql);
     }
 
@@ -368,34 +372,46 @@ create_new_database(const gchar *filename)
 }
 
 static gboolean
-execute_non_query(const gchar *sql)
+execute_non_query(const gchar *sql, int (*bind)(sqlite3_stmt *))
 {
-    sqlite3 *db;
+    sqlite3 *db = get_database();
     sqlite3_stmt *stmt;
-    gchar *data_file;
     gboolean result;
 
     result = FALSE;
-    data_file = eom_get_data_file();
-
-    if (check_and_create_database(data_file)) {
-        if (sqlite3_open(data_file, &db) == SQLITE_OK) {
-            int res =
-                sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
 
-            if (res == SQLITE_OK) {
-                if (sqlite3_step(stmt) == SQLITE_DONE)
-                    result = TRUE;
-            }
+    if (db) {
+        int res =
+            sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
 
-            sqlite3_finalize(stmt);
+        if (res == SQLITE_OK) {
+            if (bind && bind(stmt) && sqlite3_step(stmt) == SQLITE_DONE)
+                result = TRUE;
         }
+
+        sqlite3_finalize(stmt);
         sqlite3_close(db);
     }
 
     return result;
 }
 
+static sqlite3 *
+get_database(void)
+{
+    sqlite3 *db;
+    gchar *data_file = eom_get_data_file();
+
+    if (check_and_create_database(data_file)) {
+        if (sqlite3_open(data_file, &db) == SQLITE_OK)
+            return db;
+        else
+            sqlite3_close(db);
+    }
+
+    return NULL;
+}
+
 static GList *
 get_manga_for_query(const gchar *query)
 {
-- 
cgit v1.2.3-54-g00ecf