Javascript array in SQL prepared statement

Question

Here is what I am trying to do. I have a simple piece of java script code which takes the value of a checkbox and deletes the corresponding from the database.. Say that I have three options red, blue, yellow displayed and red is selected, the record for red will be deleted.

This code does it perfectly.

var n = $('input[type=checkbox]:checked').val();
db.transaction(function (tx) {
                                tx.executeSql('delete from details where name = ?',[n],function(){
                                alert('The event is successfully deleted');
                            });

                        });

But the requirement is to capture multiple checkbox selections as well (i.e) if both red and blue are selected or all the three are selected, the code must deleted the records of the selected values. I am able to capture the values selected in an array with the following piece of code.

var checkboxValues = $('.myCheckbox:checked').map(function() {
                        return $(this).val();
                    }).get();

Now i need to use this array of strings in a SQL statement. I tried the following piece of code but of no use.

db.transaction(function (tx) {
                                tx.executeSql('delete from details where name in (?)',[checkboxValues],function(){
                                alert('The event is successfully deleted');
                            });

A few other answers suggested me to convert the array object into a comma-seperated string using toString() and join() function but those did not quite help me. I doubt whether my SQL statement is correct. Pls someone help me. Thanks in advance !!

You are generating and executing SQL queries from your javascript? Hopefully your website is not publicly accessible. — Darin Dimitrov
– Darin Dimitrov, Commented Jun 16, 2013 at 16:41
Oh dear lord if you are building a library to execute sql statements client-side then you are allowing your users to take control of your database. — Matthew Cox
– Matthew Cox, Commented Jun 16, 2013 at 16:44
So true ... so, I hope your website just isn't accessible other than by you. — Matthew Cox
– Matthew Cox, Commented Jun 16, 2013 at 16:47
This isn't a website at first place. This is just a mobile application running on phonegap framework. Thanks for your concern. Any help guys ? — bala
– bala, Commented Jun 16, 2013 at 16:56

James Sheils · Accepted Answer · 2013-06-16 20:23:24Z

2

Maybe you could turn the function around so that it iterates over the returned values and preforms the db function for each in turn. Something along the lines of:

    $(checkboxValues).each(function(tx){...

Or you could consider preforming the SQL update as each value is found within the first function, as long as you are throwing security out the window. Then you would only need to include the value as a this and you could lose the variable.

answered Jun 16, 2013 at 20:23

James Sheils

2763 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bala Over a year ago

I have already implemented this work around by looping through the array. But what i need to know is,how to get the 'in' clause working with SQL prepared statement in javascript so that I can work with array in entirety. Thanks for the help !!

James Sheils Over a year ago

In the long run, I think it will be easier for you implement your db manipulations from the server. Otherwise you are going to be building work-around upon work-around resulting in a code that cannot be maintained, (but is easily hackable).

Collectives™ on Stack Overflow

Javascript array in SQL prepared statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related