Changeset 15849

Timestamp:

2008-06-04T14:14:48+12:00 (16 years ago)

Author:

mdewsnip

Message:

(Adding new DB support) Adding a "sqlite_safe()" function for escaping any single quotes in values going into SQL statements, and applied this everywhere it might be required.

Location:

gsdl/trunk/lib

Files:

• sqlitedbclass.cpp (modified) (8 diffs)
• sqlitedbclass.h (modified) (1 diff)

gsdl/trunk/lib/sqlitedbclass.cpp

@@ -93 +93 @@
 void sqlitedbclass::deletekey (const text_t &key)
 {
-  text_t sql_cmd = "DELETE FROM data WHERE key='" + key + "'";
+  text_t sql_cmd = "DELETE FROM data WHERE key='" + sqlite_safe(key) + "'";
   sqlexec(sql_cmd);
 }
@@ -116 +116 @@
   {
     // No sorting required
-    sql_cmd = "SELECT docOID FROM document_metadata WHERE element='" + metadata_element_name + "' AND value='" + metadata_value + "'";
+    sql_cmd = "SELECT docOID FROM document_metadata WHERE element='" + sqlite_safe(metadata_element_name) + "' AND value='" + sqlite_safe(metadata_value) + "'";
   }
   else
@@ -122 +122 @@
     // Sort the documents by a certain metadata element
     // John Thompson thinks this may not be the most efficient solution, and recommends using ON instead of WHERE
-    sql_cmd = "SELECT b.docOID FROM document_metadata AS a LEFT JOIN document_metadata AS b USING (docOID) WHERE a.element='" + metadata_element_name + "' AND a.value='" + metadata_value + "' AND b.element='" + sort_by_metadata_element_name + "' ORDER BY b.value";
+    sql_cmd = "SELECT b.docOID FROM document_metadata AS a LEFT JOIN document_metadata AS b USING (docOID) WHERE a.element='" + sqlite_safe(metadata_element_name) + "' AND a.value='" + sqlite_safe(metadata_value) + "' AND b.element='" + sqlite_safe(sort_by_metadata_element_name) + "' ORDER BY b.value";
   }
   vector<text_tmap> sql_results;
@@ -153 +153 @@
 bool sqlitedbclass::getkeydata (const text_t& key, text_t &data)
 {
-  text_t sql_cmd = "SELECT value FROM data WHERE key='" + key + "'";
+  text_t sql_cmd = "SELECT value FROM data WHERE key='" + sqlite_safe(key) + "'";
   vector<text_tmap> sql_results;
   if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
@@ -204 +204 @@
 
   // Get the entries in the "document_metadata" table where the element matches that specified
-  text_t sql_cmd = "SELECT value FROM document_metadata WHERE element='" + metadata_element_name + "'";
+  text_t sql_cmd = "SELECT value FROM document_metadata WHERE element='" + sqlite_safe(metadata_element_name) + "'";
   vector<text_tmap> sql_results;
   if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
@@ -235 +235 @@
   else
   {
-    text_t sql_cmd = "UPDATE data SET value='" + data + "' WHERE key='" + key + "'";
+    text_t sql_cmd = "UPDATE data SET value='" + sqlite_safe(data) + "' WHERE key='" + sqlite_safe(key) + "'";
     return sqlexec(sql_cmd);
   }
@@ -253 +253 @@
   usleep(m);
 #endif
+}
+
+
+text_t sqlitedbclass::sqlite_safe (const text_t &value_arg)
+{
+  text_t value = value_arg;
+  value.replace("'", "''");
+  return value;
 }
 
@@ -346 +354 @@
 bool sqlitedbclass::sqltableexists(const text_t &table_name)
 {
-  text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + table_name + "'";
+  text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + sqlite_safe(table_name) + "'";
   vector<text_tmap> sql_results;
   if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)

gsdl/trunk/lib/sqlitedbclass.h

@@ -69 +69 @@
   sqlite3* sqlitefile;
 
+  text_t sqlite_safe (const text_t &value_arg);
+
   bool sqlexec (const text_t &sql_cmd);
   bool sqlgetarray (const text_t &sql_cmd, vector<text_tmap> &sql_results);