Java string interpreted wrongly in SQL

Question

There is an string[] likely; This array stores the name of column of database table dynamically while runtime.And the program understand the size of the likely in runtime. Now to put this in sql query .I use a for loop for concatanation of string

for(int k=0;k<likely.length;k++)
    {
        temp1="\"+likely["+k+"]+\"='Likely' AND ";
        temp=temp.concat(temp1);                
    }

if the size is 3 the final temp will look like

temp = " "+likely[0]+"='Likely' AND "+
    likely[1]+"='Likely' AND "+
    likely[2]+"='Likely' AND "

Now i formulate sql query as

sql ="SELECT * FROM PUNE WHERE"+temp+"Arts_And_Museum='Yes'";

But during the

ps = con.prepareStatement(sql);

this statement is compiled like

SELECT * FROM PUNE 
WHERE [+likely[0]+]='Likely' 
AND [+likely[1]+]='Likely' 
AND [+likely[2]+]='Likely' AND Arts_And_Museum='Yes'

After deep investigation ,I came to conclusion that it interprets \" as [ or ] alternately..

As a result i get an error How should i solve this problem?

I run a for loop and prepare a string I am trying to write a sql syntax

Ozzy · Accepted Answer · 2012-04-14 23:55:37Z

1

This is why you should use parameterized inputs when dealing with SQL queries.

// conn refers to your database connection
PreparedStatement stmnt = null;
ResultSet rs = null;
try {
    stmnt = conn.prepareStatement("SELECT * FROM tbl WHERE col > '?'");
    stmnt.setInt(1, 300); //set first parameter to 300
    rs = stmnt.executeQuery();
} catch(Exception ex) {
    System.err.println("Database exception: " + ex.getMessage());
}

answered Apr 14, 2012 at 23:55

Ozzy

8,3327 gold badges58 silver badges96 bronze badges

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

1 Comment

Ozzy Over a year ago

When you parameterize any insert value it will not be parsed as SQL

logan · Accepted Answer · 2012-04-14 23:36:53Z

0

\ is a reserved character. If you want to output a quote in your string you use \".

So, this code:

temp1="\"+likely["+k+"]+\"='Likely' AND ";

Will return this string:

"+likely1]+"='Likely' AND

It seems that your sql is transforming " into [ or ]

answered Apr 14, 2012 at 23:36

logan

3,3752 gold badges35 silver badges45 bronze badges

Comments

Starx · Accepted Answer · 2012-04-14 23:47:48Z

0

The symbol \ is used for escaping. On doing this, you are escaping all the front characters.

Whenever you are asking for an item in array you can access it using likely[k] no need for likey["k"]

Here is how you should do it.

temp1="\\"+likely[k]+"\\='Likely' AND ";

answered Apr 14, 2012 at 23:47

Starx

79.3k50 gold badges187 silver badges266 bronze badges

Comments

Mosty Mostacho · Accepted Answer · 2012-04-14 23:56:16Z

0

Just change this line:

temp1="\"+likely["+k+"]+\"='Likely' AND ";

To this one:

temp1="\"" + likely[k] + "\"='Likely' AND ";

answered Apr 14, 2012 at 23:56

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

1 Comment

Mosty Mostacho Over a year ago

@ShwetaB.Patil Welcome ^.^ Anyway, there is no need to thanks, that's what accepting answers is there for :)

Collectives™ on Stack Overflow

Java string interpreted wrongly in SQL

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related