* Account server now loads item database on startup and synchronizes tmw_items table in the database.

* Added method to DataProvider to escape quotes in strings so that they can be used in SQL statements.

7 files changed, 112 insertions, 17 deletions

diff --git a/ChangeLog b/ChangeLog
index 053caef6..fa68e6f9 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -4,8 +4,10 @@
 	src/account-server/serverhandler.cpp, src/defines.h, 
 	src/game-server/accountconnection.cpp, src/game-server/itemmanager.cpp,
 	src/game-server/itemmanager.hpp: Added version information to item database.
-	Gameserver reports its local version to accountserver and gets notified 
-	if the verion is up-to-date or outdated to prevent inconsistencies.
+	Game server reports its local version to account server and gets notified 
+	if the version is up-to-date or outdated to prevent inconsistencies.
+	Account server initially loads item database on startup and synchronizes
+	tmw_items table in the database.
 
 2008-11-04  David Athay  <ko2fan@gmail.com>
 
diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp
index a928d1bf..4a39b39c 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -46,7 +46,8 @@
  * Constructor.
  */
 DALStorage::DALStorage()
-        : mDb(dal::DataProviderFactory::createDataProvider())
+        : mDb(dal::DataProviderFactory::createDataProvider()),
+          mItemDbVersion(0)
 {
 }
 
@@ -88,6 +89,8 @@ void DALStorage::open()
 
         //TODO: check database version here
 
+        // synchronize base data from xml files
+        SyncDatabase();
     }
     catch (const DbConnectionFailure& e) {
         LOG_ERROR("(DALStorage::open #1) Unable to connect to the database: "
@@ -1446,16 +1449,13 @@ void DALStorage::deletePost(Letter* letter)
     }
 }
 
-unsigned int DALStorage::getItemDatabaseVersion(void)
+void DALStorage::SyncDatabase(void)
 {
-    int version;
-
-    // TODO: make data/items.xml a constant or read it from config file
     xmlDocPtr doc = xmlReadFile(DEFAULT_ITEM_FILE, NULL, 0);
     if (!doc)
     {
         LOG_ERROR("Item Manager: Error while parsing item database (items.xml)!");
-        return 0;
+        return;
     }
 
     xmlNodePtr node = xmlDocGetRootElement(doc);
@@ -1463,9 +1463,11 @@ unsigned int DALStorage::getItemDatabaseVersion(void)
     {
         LOG_ERROR("Item Manager:(items.xml) is not a valid database file!");
         xmlFreeDoc(doc);
-        return 0;
+        return;
     }
 
+    mDb->beginTransaction();
+    int itmCount = 0;
     for (node = node->xmlChildrenNode; node != NULL; node = node->next)
     {
         // Try to load the version of the item database. The version is defined
@@ -1480,16 +1482,66 @@ unsigned int DALStorage::getItemDatabaseVersion(void)
             {
                 LOG_ERROR("Itemdatabase has wrong version format string!");
                 xmlFreeDoc(doc);
-                return 0;
+                continue;
             }
+
             // position 11 is the first numeric character in the SVN tag
-            version = atoi(revision.substr(11).c_str());
+            mItemDbVersion = atoi(revision.substr(11).c_str());
+            LOG_INFO("Loading item database version " << mItemDbVersion);
+        }
 
-            LOG_INFO("Loading item database version " << version);
-            xmlFreeDoc(doc);
-            return version;
+        if (!xmlStrEqual(node->name, BAD_CAST "item"))
+        {
+            continue;
+        }
+
+        if (xmlStrEqual(node->name, BAD_CAST "item"))
+        {
+            int id = XML::getProperty(node, "id", 0);
+            if (id < 500)
+            {
+                continue;
+            }
+
+            int weight = XML::getProperty(node, "weight", 0);
+            std::string type = XML::getProperty(node, "type", "");
+            std::string name = XML::getProperty(node, "name", "");
+            std::string desc = XML::getProperty(node, "description", "");
+            std::string eff  = XML::getProperty(node, "effect", "");
+            std::string image = XML::getProperty(node, "image", "");
+
+            try
+            {
+                std::ostringstream sql;
+                sql << "UPDATE " << ITEMS_TBL_NAME
+                    << " SET name = '" << mDb->escapeSQL(name) << "', "
+                    << "     description = '" << mDb->escapeSQL(desc) << "', "
+                    << "     image = '" << image << "', "
+                    << "     weight = " << weight << ", "
+                    << "     itemtype = '" << type << "', "
+                    << "     effect = '" << mDb->escapeSQL(eff) << "', "
+                    << "     dyestring = '' "
+                    << " WHERE id = " << id;
+
+                mDb->execSql(sql.str());
+                if (mDb->getModifiedRows() == 0)
+                {
+                    sql.str("");
+                    sql << "INSERT INTO " << ITEMS_TBL_NAME
+                        << "  VALUES ( " << id << ", '" << name << "', '"
+                        << desc << "', '" << image << "', " << weight << ", '"
+                        << type << "', '" << eff << "', '' )";
+                    mDb->execSql(sql.str());
+                }
+                itmCount++;
+            }
+            catch (dal::DbSqlQueryExecFailure const &e)
+            {
+                LOG_ERROR("(DALStorage::SyncDatabase) SQL query failure: " << e.what());
+            }
         }
     }
+
+    mDb->commitTransaction();
     xmlFreeDoc(doc);
-    return 0;
 }
diff --git a/src/account-server/dalstorage.hpp b/src/account-server/dalstorage.hpp
index 5a879bf6..638e4d27 100644
--- a/src/account-server/dalstorage.hpp
+++ b/src/account-server/dalstorage.hpp
@@ -336,7 +336,8 @@ class DALStorage
          *
          * @return Version of the item database.
          */
-        unsigned int getItemDatabaseVersion(void);
+        unsigned int getItemDatabaseVersion(void) const
+        { return mItemDbVersion; }
 
     private:
         /**
@@ -371,7 +372,18 @@ class DALStorage
          */
         Character *getCharacterBySQL(std::string const &query, Account *owner);
 
+        /**
+         * Synchronizes the base data in the connected SQL database with the xml
+         * files like items.xml.
+         * This method is called once after initialization of DALStorage.
+         * Probably this function should be called if a gm requests an online
+         * reload of the xml files to load new items or monsters without server
+         * restart.
+         */
+        void SyncDatabase(void);
+
         dal::DataProvider *mDb; /**< the data provider */
+        unsigned int mItemDbVersion;    /**< Version of the item database. */
 };
 
 extern DALStorage *storage;
diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp
index 7c4f6df5..278ecfa2 100644
--- a/src/account-server/dalstoragesql.hpp
+++ b/src/account-server/dalstoragesql.hpp
@@ -84,6 +84,11 @@ static char const *CHAR_SKILLS_TBL_NAME = "tmw_char_skills";
 static char const *INVENTORIES_TBL_NAME("tmw_inventories");
 
 /**
+ * TABLE: tmw_items.
+ */
+static char const *ITEMS_TBL_NAME("tmw_items");
+
+/**
  * TABLE: tmw_guilds.
  * Store player guilds
  */
diff --git a/src/dal/dataprovider.cpp b/src/dal/dataprovider.cpp
index 93de865c..4092c6bf 100644
--- a/src/dal/dataprovider.cpp
+++ b/src/dal/dataprovider.cpp
@@ -22,6 +22,7 @@
 
 
 #include "dataprovider.h"
+#include "../utils/logger.h"
 
 namespace dal
 {
@@ -74,4 +75,19 @@ DataProvider::getDbName(void)
 }
 
 
+std::string& DataProvider::escapeSQL(std::string &sql)
+{
+    size_t pos = 0;
+
+    pos = sql.find("'", pos);
+    while (pos != std::string::npos)
+    {
+        sql.replace(pos, 1, "\'\'");
+        pos += 2;
+        pos = sql.find("'", pos);
+    }
+
+    return sql;
+}
+
 } // namespace dal
diff --git a/src/dal/dataprovider.h b/src/dal/dataprovider.h
index 2c0a9de4..7cbc28b9 100644
--- a/src/dal/dataprovider.h
+++ b/src/dal/dataprovider.h
@@ -180,6 +180,14 @@ class DataProvider
         virtual const unsigned int
         getLastId(void) const = 0;
 
+        /**
+         * Takes a SQL snippet and escapes special caharacters like ' to prevent
+         * SQL injection attacks.
+         *
+         * @param sql SQL Snippet to escape.
+         */
+        std::string& escapeSQL(std::string &sql);
+
     protected:
         std::string mDbName;  /**< the database name */
         bool mIsConnected;    /**< the connection status */
diff --git a/src/dal/sqlitedataprovider.cpp b/src/dal/sqlitedataprovider.cpp
index feb85760..77b5a5f5 100644
--- a/src/dal/sqlitedataprovider.cpp
+++ b/src/dal/sqlitedataprovider.cpp
@@ -153,7 +153,7 @@ SqLiteDataProvider::execSql(const std::string& sql,
         if (errCode != SQLITE_OK) {
             std::string msg(sqlite3_errmsg(mDb));
 
-            LOG_ERROR("Error in SQL: " << msg);
+            LOG_ERROR("Error in SQL: " << sql << "\n" << msg);
 
             // free memory
             sqlite3_free_table(result);