Using an array in SQL IN

Question

Two part question...(note that I'm using a PostGres)

My SQL query is formatted like this:

$.ajax({
        url: "https://something?q=SELECT *database_final_form_merge where territory in ("+terrs+")",
        type: 'GET',
        dataType: 'JSON',
     success: function(data) {
      }
       });

The variable terrs is an array like this:

["D1VE3011", "D1VE3011", "D1VD2209", "D1VD2209", "D1VD2103", "D1VD2103"]

This formats the SQL query like this though:

SELECT* from database_final_form_merge where territory IN (D1VE3011,D1VE3011,D1VD2209,D1VD2209,D1VD2103,D1VD2103)

But it needs to be in this format (I think):

SELECT* from database_final_form_merge where territory IN ('D1VE3011','D1VE3011','D1VD2209','D1VD2209','D1VD2103','D1VD2103')

This works when I try it directly without an AJAX GET. Is there a different way I should be passing this array?

That's question 1.

Question 2...is there a way to pass that array so that only unique values are passed? You'll note that in my array there are duplicates, but wondering if there's a way to only pass along unique values.

Thanks.

That is the perfect, self-serving recipe for SQL injections. — vitaly-t
– vitaly-t, Commented Jan 8, 2016 at 2:31
@vitaly-1 I'm passing this to a SaaS platform that steralizes everything before running the queries to ensure this doesn't happen — jonmrich
– jonmrich, Commented Jan 8, 2016 at 2:33
Do you have the authority to create a table on the database? If so, depending on how large the lists can get, you might get significant gains in efficiency by loading your values into a table (using some form of PostgreSQL's copy command, which is lightning-quick) and doing a join or a semi-join on that table. If you have, for example, 1,000 entries, I'll bet the time of compiling the statement alone will be signficiant compared to this approach. — Hambone
– Hambone, Commented Jan 8, 2016 at 3:03

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Let's put passing query as a parameter aside and get into the problem.

For the question 2 you can use jQuery.unique

And for the former question:

"('" + terrs.join("','") + "')" generates ('D1VE3011','D1VE3011','D1VD2209','D1VD2209','D1VD2103','D1VD2103') part.

Mind the white spaces though. You might end up with string like this

'(' D1VD2209',' D1VD2103','D1VD2103 ')

*EDITED accordingly

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 8, 2016 at 2:41

Atu

9371 gold badge12 silver badges22 bronze badges

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

3 Comments

jonmrich Over a year ago

I'm not using PHP, so using $_GET['arr'] won't work. Is there another way to format?

Atu Over a year ago

oh... or simply replacing the ("+terrs+")" with ('" + terrs.join("','") + "')" should work?

jonmrich Over a year ago

This was the simple solution. I created a new variable that did the join first and then passed that into my AJAX call.

Collectives™ on Stack Overflow

Using an array in SQL IN

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related