CODEWITHSUNDEEP
javascripthtmlsqlite
md123
md123

Reputation: 136

HTML5 SQL Insert Multiple Rows

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

Upvotes: 1

Views: 254

Answers (1)

ForguesR
ForguesR

Reputation: 3618

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 (?,?,?,?),(?,?,?,?),(?,?,?,?)

Upvotes: 1

Related Questions