Changed database to using prepared statements, to stop SQL injection attacks

4 files changed, 128 insertions, 7 deletions

diff --git a/src/dal/dataprovider.cpp b/src/dal/dataprovider.cpp
index e903d0c..8fd8fe0 100644
--- a/src/dal/dataprovider.cpp
+++ b/src/dal/dataprovider.cpp
@@ -71,7 +71,7 @@ DataProvider::getDbName(void)
     return mDbName;
 }
 
-
+/*
 std::string& DataProvider::escapeSQL(std::string &sql)
 {
     size_t pos = 0;
@@ -86,5 +86,6 @@ std::string& DataProvider::escapeSQL(std::string &sql)
 
     return sql;
 }
+*/
 
 } // namespace dal
diff --git a/src/dal/dataprovider.h b/src/dal/dataprovider.h
index f950949..65de8ee 100644
--- a/src/dal/dataprovider.h
+++ b/src/dal/dataprovider.h
@@ -178,12 +178,37 @@ class DataProvider
         getLastId(void) const = 0;
 
         /**
-         * Takes a SQL snippet and escapes special caharacters like ' to prevent
-         * SQL injection attacks.
-         *
-         * @param sql SQL Snippet to escape.
+         * Prepare SQL statement
+         */
+        virtual bool prepareSql(const std::string &sql) = 0;
+
+        /**
+         * Process SQL statement
+         * SQL statement needs to be prepared and parameters binded before
+         * calling this function
+         */
+        virtual const RecordSet& processSql() = 0;
+
+        /**
+         * Bind String
+         * @param place - which parameter to bind to
+         * @param value - the string to bind
+         */
+        virtual void bindString(int place, const std::string &value) = 0;
+
+        /**
+         * Bind Integer
+         * @param place - which parameter to bind to
+         * @param value - the integer to bind
+         */
+        virtual void bindInteger(int place, int value) = 0;
+
+        /**
+         * Bind Float
+         * @param place - which parameter to bind to
+         * @param value - the float to bind
          */
-        std::string& escapeSQL(std::string &sql);
+        virtual void bindFloat(int place, float value) = 0;
 
     protected:
         std::string mDbName;  /**< the database name */
diff --git a/src/dal/sqlitedataprovider.cpp b/src/dal/sqlitedataprovider.cpp
index 1eaac78..accf979 100644
--- a/src/dal/sqlitedataprovider.cpp
+++ b/src/dal/sqlitedataprovider.cpp
@@ -370,4 +370,65 @@ SqLiteDataProvider::getLastId(void) const
     return (unsigned int)lastId;
 }
 
+bool SqLiteDataProvider::prepareSql(const std::string &sql)
+{
+    if (!mIsConnected)
+        return false;
+
+    LOG_DEBUG("Preparing SQL statement: "<<sql);
+
+    mRecordSet.clear();
+
+    if (sqlite3_prepare_v2(mDb, sql.c_str(), sql.size(), &mStmt, NULL) != SQLITE_OK)
+    {
+        return false;
+    }
+
+    return true;
+}
+
+const RecordSet& SqLiteDataProvider::processSql()
+{
+    if (!mIsConnected) {
+        throw std::runtime_error("not connected to database");
+    }
+
+    int totalCols = sqlite3_column_count(mStmt);
+    Row fieldNames;
+
+    while (sqlite3_step(mStmt) == SQLITE_ROW)
+    {
+        Row r;
+        for (int col = 0; col < totalCols; ++col)
+        {
+            fieldNames.push_back(sqlite3_column_name(mStmt, col));
+            r.push_back((char*)sqlite3_column_text(mStmt, col));
+        }
+        // ensure we set column headers before adding a row
+        mRecordSet.setColumnHeaders(fieldNames);
+        mRecordSet.add(r);
+    }
+
+
+
+    sqlite3_finalize(mStmt);
+
+    return mRecordSet;
+}
+
+void SqLiteDataProvider::bindString(int place, const std::string &value)
+{
+    sqlite3_bind_text(mStmt, place, value.c_str(), value.size(), SQLITE_STATIC);
+}
+
+void SqLiteDataProvider::bindInteger(int place, int value)
+{
+    sqlite3_bind_int(mStmt, place, value);
+}
+
+void SqLiteDataProvider::bindFloat(int place, float value)
+{
+    sqlite3_bind_double(mStmt, place, value);
+}
+
 } // namespace dal
diff --git a/src/dal/sqlitedataprovider.h b/src/dal/sqlitedataprovider.h
index 8950f31..3f1951d 100644
--- a/src/dal/sqlitedataprovider.h
+++ b/src/dal/sqlitedataprovider.h
@@ -146,6 +146,39 @@ class SqLiteDataProvider: public DataProvider
         const unsigned int
         getLastId(void) const;
 
+        /**
+         * Prepare SQL statement
+         */
+        bool prepareSql(const std::string &sql);
+
+        /**
+         * Process SQL statement
+         * SQL statement needs to be prepared and parameters binded before
+         * calling this function
+         */
+        const RecordSet& processSql();
+
+        /**
+         * Bind String
+         * @param place - which parameter to bind to
+         * @param value - the string to bind
+         */
+        void bindString(int place, const std::string &value);
+
+        /**
+         * Bind Integer
+         * @param place - which parameter to bind to
+         * @param value - the integer to bind
+         */
+        void bindInteger(int place, int value);
+
+        /**
+         * Bind Float
+         * @param place - which parameter to bind to
+         * @param value - the float to bind
+         */
+        void bindFloat(int place, float value);
+
     private:
 
         /** defines the name of the database config parameter */
@@ -162,7 +195,8 @@ class SqLiteDataProvider: public DataProvider
         const bool
         inTransaction(void) const;
 
-        sqlite3* mDb; /**< the handle to the database connection */
+        sqlite3 *mDb; /**< the handle to the database connection */
+        sqlite3_stmt *mStmt; /**< the prepared statement to process */
 };