Escape-Problems while synchronising Data

[mynote]

Hello, i've a little problem.

I'm synchronising data from web to my App using WebSQLSync.

Here's a part of my Code to get it working:

var SYNCDATA = {
        url: 'https://api.seiboldsoft.de/milchbar2000/sync/all', //TODO Set your server URL
        database: null,
        tableToSync: [{
                tableName: 'coupons',
            }, {
                tableName: 'events'
            }, {
                tableName: 'news',
            }],
        sync_info: {//Example of user info
            userEmail: '[email protected]', //the user mail is not always here
            device_uuid: 'UNIQUE_DEVICE_ID_287CHBE873JB', //if no user mail, rely on the UUID
            lastSyncDate: 0,
            device_version: '5.1',
            device_name: 'test navigator',
            userAgent: navigator.userAgent,
            appName: 'de-DE',
            mosa_version: '3.2',
            lng: 'de'
        },
        dropTables: function (transaction) {
            transaction.executeSql('DROP TABLE IF EXISTS coupons;', [], self._nullDataHandler, self._errorHandler);
            transaction.executeSql('DROP TABLE IF EXISTS events;', [], self._nullDataHandler, self._errorHandler);
            transaction.executeSql('DROP TABLE IF EXISTS news;', [], self._nullDataHandler, self._errorHandler);
        },
        initLiveDb: function (callBack) {

            var self = this;
            if (self.database === null) {
                self.database = openDatabase('localMilchbar2000DB', '1.0', 'Milchbar2000 DB Sync', 4 * 1024 * 1024);
            }
            self.database.transaction(function (transaction) {
                transaction.name = 'initLiveDb general tx';
                self.dropTables(transaction);
                transaction.executeSql('CREATE TABLE IF NOT EXISTS coupons (' +
              ..... truncated
                  'PRIMARY KEY ("id"));', [], self._nullDataHandler, self._errorHandler);

This is working fine if i call dropTables before i create the Tables. Usually this shouldnt matter. Anyway, if i dont drop the Tables before and call the Sync again (and deliver the same data) i'm getting an error with:

`` (index):101 Error : could not prepare statement (1 near "image": syntax error) (Code 5) Transaction.sql = undefined

Now i guess the reason is that the id is already inserted? Should i don't send the incremented ID from the server and let the client increase it by itself? The only problem i have is, that the error "near image" and "near btn" appears when this is in the sync data:

<img class="image-container" and <span class="btn btn-negative".

Do i need to escape it serverside before sending? I dont want to drop the Tables every time again to sync all data :)

Thank you

edit: is must be while the updating process since it doesnt escape the values:

 _buildUpdateSQL: function (tableName, objToUpdate) {
            /*ex UPDATE "nom de table" SET colonne 1 = [valeur 1], colonne 2 = [valeur 2] WHERE {condition}*/
            var self = this;
            var sql = 'UPDATE ' + tableName + ' SET ';
            var members = self._getAttributesList(objToUpdate);
            if (members.length === 0) {
                throw 'buildUpdateSQL : Error, try to insert an empty object in the table ' + tableName;
            }
            var values = self._getMembersValue(objToUpdate, members);

            var nb = members.length;
            for (var i = 0; i < nb; i++) {
                sql += '"' + members[i] + '" = "' + values[i] + '"';
                if (i < nb - 1) {
                    sql += ', ';
                }
            }

            return sql;
        },

fixed. Escaping values[i].

` _buildUpdateSQL: function (tableName, objToUpdate) { /ex UPDATE "nom de table" SET colonne 1 = [valeur 1], colonne 2 = [valeur 2] WHERE {condition}/

        var self = this;
        var sql = 'UPDATE ' + tableName + ' SET ';
        var members = self._getAttributesList(objToUpdate);
        if (members.length === 0) {
            throw 'buildUpdateSQL : Error, try to insert an empty object in the table ' + tableName;
        }
        var values = self._getMembersValue(objToUpdate, members);
        var nb = members.length;
        for (var i = 0; i < nb; i++) {
            sql += '"' + members[i] + '" = "' + self._sql_escape_string(values[i]) + '"';
            if (i < nb - 1) {
                sql += ', ';
            }
        }

        return sql;
    },
    _sql_escape_string: function (val) {
        if (val == null) {
            return;
        }
        val = val.replace(/[\0\n\r\b\t\\'"\x1a]/g, function (s) {
            switch (s) {
                case "\0":
                    return "\\0";
                case "\n":
                    return "\\n";
                case "\r":
                    return "\\r";
                case "\b":
                    return "\\b";
                case "\t":
                    return "\\t";
                case "\x1a":
                    return "\\Z";
                case "'":
                    return "''";
                case '"':
                    return '""';
                default:
                    return "\\" + s;
            }
        });
        return val;
    },`

[mynote]

Fixed in https://github.com/mynote/WebSqlSync/commit/14d67f08487f9544dd95b45d50ec01e5eb88507f

[orbitaloop]

Hi, sorry for not answering before (very busy). I can't see your commit (the url is not working). Perhaps you can do a pull request?

[abeauseigle]

See https://github.com/abeauseigle/TodoApp2