语句字符串中'？'的数量与参数计数不匹配

I'm trying to make a synchronism with my API using WebSQL, but I'm getting the following error:

我正在尝试使用WebSQL与我的API同步,但是我收到以下错误:

number of '?'s in statement string does not match argument count.

语句字符串中'?'的数量与参数计数不匹配。

I was following this tutorial

我正在学习本教程

I don't know what is going on, what am I missing here?

我不知道发生了什么,我在这里错过了什么?

function createTable() {
            db.transaction(
                function (tx) {

                    tx.executeSql('CREATE TABLE IF NOT EXISTS COUNTRY (id, countryName, countryCode)');
                    tx.executeSql('CREATE TABLE IF NOT EXISTS LOCATION (id, locationName)');
                    tx.executeSql("CREATE TABLE IF NOT EXISTS clientes (" +
                    "clientes_id Integer PRIMARY KEY AUTOINCREMENT, " +
                    "_criado Text, " +
                    "_modificado Text, " +
                    "_status Text, " +
                    "id_rm Integer, " +
                    "credencial_id Integer, " +
                    "informacao_adicional, " +
                    "nome, " +
                    "tipo, " +
                    "CONSTRAINT unique_clientes_id UNIQUE ('clientes_id'))");
                    tx.executeSql('CREATE INDEX IF NOT EXISTS "clientes.index_clientes_id" ON "clientes"("clientes_id");');



                },
                txErrorHandler,
                function () {
                    log('clientes table created successfully');
                }
            );
        }
function getLastSync() {
            db.transaction(
                function (tx) {
                    var sql = "SELECT MAX(_modificado) as lastS FROM clientes";


                    tx.executeSql(sql,
                        function (tx, results) {


                            var lastSync = results.rows.item(0).lastS;
                            console.log(lastSync);

                        }
                    );

                },
                txErrorHandler,
                function () {
                    log('error');
                }
            );
        }

        function getChanges(syncURL, modifiedSince) {


            ServiceClientes.getAll(syncURL, modifiedSince).success(function (data) {
                log("The server returned " + data.length + " changes that occurred after " + modifiedSince);
            }).error(function (error) {
                console.log(error);
            });


        }

        function applyChanges() {

            angular.forEach(data.dados, function (item) {
                db.transaction(
                    function (tx) {
                        log(item.nome + " : " + item.tipo_pessoa);

                        tx.executeSql('INSERT OR REPLACE INTO clientes (nome, tipo, ' +
                        '_criado,' +
                        '_modificado , ' +
                        '_status, ' +
                        'id_rm, ' +
                        'informacao_adicional ) VALUES (?,?,?,?,?,?,?)',
                        [item.nome, item.tipo_pessoa, item.criado, item.modificado, item.status, item.id, item.informacoes_adicionais]);
                    },
                    txErrorHandler,
                    function (tx) {
                    }
                );
            });
        }

        function sync(syncURL) {


            getLastSync(function (lastSync) {
                getChanges(syncURL, lastSync, function (changes) {
                        if (changes.length > 0) {
                            applyChanges(changes);
                        } else {
                            console.log('Nothing to synchronize');
                        }

                    }
                );
            });

        }

2 个解决方案

#1

You should utilise second argument of executeSql to pass your data into parametrised SQL query, rather than concatenate them. Concatenating is a bad practise and you may have a quote or question mark in one of the fields which could result in error you describe.

您应该利用executeSql的第二个参数将数据传递给参数化的SQL查询,而不是将它们连接起来。连接是一种不好的做法,您可能在其中一个字段中有引号或问号,这可能会导致您描述的错误。

So use something like:

所以使用类似的东西:

tx.executeSql('INSERT OR REPLACE INTO clientes (nome, tipo, ' +
              '_criado,' +
              '_modificado , ' +
              '_status, ' +
              'id_rm, ' +
              'informacao_adicional ) VALUES (?,?,?,?,?,?,?)',
       [item.nome, item.tipo_pessoa, item.criado, item.modificado, item.status, item.id, item.informacoes_adicionais]);

#2

I can see two problems here:

我在这里可以看到两个问题:

1. executeSql takes 3 parameters: sql, params, callback. Both params and callback are optional but if you want to specify callback you HAVE TO specify params too.

executeSql有3个参数:sql,params,callback。 params和callback都是可选的,但如果你想指定回调,你也必须指定params。

2. Don't concatenate values provided by user. You can end up with sql-incetion. Instead use parameters.

   不要连接用户提供的值。你最终可以使用sql-incetion。而是使用参数。

   function createTable() {
       db.transaction(
           function (tx) {
   
               tx.executeSql('CREATE TABLE IF NOT EXISTS COUNTRY (id, countryName, countryCode)');
               tx.executeSql('CREATE TABLE IF NOT EXISTS LOCATION (id, locationName)');
               tx.executeSql("CREATE TABLE IF NOT EXISTS clientes (" +
               "clientes_id Integer PRIMARY KEY AUTOINCREMENT, " +
               "_criado Text, " +
               "_modificado Text, " +
               "_status Text, " +
               "id_rm Integer, " +
               "credencial_id Integer, " +
               "informacao_adicional, " +
               "nome, " +
               "tipo, " +
               "CONSTRAINT unique_clientes_id UNIQUE ('clientes_id'))");
               tx.executeSql('CREATE INDEX IF NOT EXISTS "clientes.index_clientes_id" ON "clientes"("clientes_id");');
   
   
   
           },
           txErrorHandler,
           function () {
               log('clientes table created successfully');
           }
       );
   }
   function getLastSync() {
       db.transaction(
           function (tx) {
               var sql = "SELECT MAX(_modificado) as lastS FROM clientes";
   
   
               tx.executeSql(sql, [],
                   function (tx, results) {
   
   
                       var lastSync = results.rows.item(0).lastS;
                       console.log(lastSync);
   
                   }
               );
   
           },
           txErrorHandler,
           function () {
               log('error');
           }
       );
   }
   
   function getChanges(syncURL, modifiedSince) {
   
   
       ServiceClientes.getAll(syncURL, modifiedSince).success(function (data) {
           log("The server returned " + data.length + " changes that occurred after " + modifiedSince);
       }).error(function (error) {
           console.log(error);
       });
   
   
   }
   
   function applyChanges() {
   
       angular.forEach(data.dados, function (item) {
           db.transaction(
               function (tx) {
                   log(item.nome + " : " + item.tipo_pessoa);
   
                   tx.executeSql('INSERT OR REPLACE INTO clientes (nome, tipo, ' +
                   '_criado,' +
                   '_modificado , ' +
                   '_status, ' +
                   'id_rm, ' +
                   'informacao_adicional ) VALUES (?,?,?,?,?,?,?)',[item.nome, item.tipo_pessoa, item.criado, item.modificado, item.status, item.id,item.informacoes_adicionais]);
               },
               txErrorHandler,
               function (tx) {
               }
           );
       });
   }
   
   function sync(syncURL) {
   
   
       getLastSync(function (lastSync) {
           getChanges(syncURL, lastSync, function (changes) {
                   if (changes.length > 0) {
                       applyChanges(changes);
                   } else {
                       console.log('Nothing to synchronize');
                   }
   
               }
           );
       });
   
   }