HTML5 SQL Insert Multiple Rows

Question

I'm trying to insert multiple rows into an SQLite database.

Single row insert like this works fine:

tx.executeSql("INSERT or REPLACE  INTO `table` (`type` , `pid` , `title` , `body`) VALUES  (?,?,?,?) ", ['qa',v.id,v.title,v.content_html ] ,console_success, console_fail);

How can I pass an array of variables into that execute to run all at once? (the paramiterizaion method seems to be properly sanitizing my data and inserting. when I do a raw query it does not and my sql fails. I need to inset a full html body in the DB )

so I can't just do a query like this

insert into table (a,b,c) values (a,b,c) ,(a,b,c),(a,b,c)

and escape() function messes up my html in the body.

P.S. NOT the same as this question HTML 5 SQLite: Multiple Inserts in One Transaction

ForguesR · Accepted Answer · 2019-10-30 12:43:35Z

As you found out, you should really stick to parametrized query and avoid "raw" queries. Parametrized queries will automatically escape the input for you thus preventing SQL injection.

The key here is to build a parametrized query that will fit your needs. Here is an example.

// Guessing 2d array of raw data
var dataArr = [ ['qa','123','title1','<html></html>' ],
                ['bc','456','title2','<html></html>' ],
                ['xy','789','title3','<html></html>' ] ];

// Creating a parametrized entry for every row of dataArr
var param = "(?,?,?,?),".repeat(dataArr.length).slice(0,-1);

// The insert query
var query = "INSERT or REPLACE  INTO `MyTable` (`type` , `pid` , `title` , `body`) VALUES ";

// Convert the 2d dataArr into 1d data
var data = [];
for(var i = 0; i < dataArr.length; i++)
  data = data.concat(dataArr[i]);

tx.executeSql(query + param, data);  // Final query is : INSERT or REPLACE  INTO `MyTable` (`type` , `pid` , `title` , `body`) VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?)

Collectives™ on Stack Overflow

HTML5 SQL Insert Multiple Rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related