Java PreparedStatement using two single quotes for an empty string parameter

Question

I am using PreparedStatement with sql, for example:

 String sql = "insert into foo (a,b,c) values (?,?,?)"; ps = conn.prepareStatement(sql); ps.setString(psIndex++, a); ps.setString(psIndex++, b); ps.setString(psIndex++, c); 

But if any of the variables is an empty string, then the resulting statement receives two single quotes. As with: VALUES ('foo','','') Then I get an exception, since the two single quotes are escape sequences.

I can’t believe that I couldn’t find anything on this, but I couldn’t. What's going on here?

Answer 1

Since the OP does not do what @Adam suggested in the comments, I will do it. This is useful for future readers. Thanks @ user119179 for the idea.

It could be a bug in the JDBC driver that we are using. The driver provider must know that '' is an escape sequence.

Actually, updating the driver seems to solve the error for the OP.

Answer 2

Like in: VALUES ('foo', '', '') Then I get an exception, because the two single quotes are escape sequences.

There is a misunderstanding. Two single quotes are an empty string. There is no escape sequence. This is a hidden quote only if it is in one separate quote. If you get an exception, this is probably elsewhere, for example, a restriction on a column in the database.

Statement

 insert into foo (a,b,c) values ('foo','','') 

- very correct SQL.

Answer 3

@cyberkiwi is right. Your columns may not be nullable. What exception do you get? If you have journal reports, share it.

Answer 4

Replace the single quote ' UNICODE \u2019 . For more information click on url

Answer 5

You did not share your Java Exception, and that would help in formulating an exact answer.

There are two aspects to your program - Java and DB restrictions.

For Java, an empty string is equal to "" (these are double quotes, not single quotes), so if your Java references - a , b & c have this value when the value is empty, I don’t see any problems with PreparedStatement , you you need to worry about the escape sequence in single quotes if you are using PreparedStatement . I'm not sure why you are listing the values ​​as ['foo','',''] , it should be ["foo","",""] .

The second aspect is that your database columns a , b & c may not allow null values ​​due to restrictions, this is a completely different matter and cannot be controlled using Java code. The database table schema must be modified to allow default values ​​in this case.

Discussion of the escape sequence with a single quote is relevant when someone prepares an SQL query by directly adding values ​​to the query string, for example, "insert into foo (a,b,c) values ('foo','','')"; or a WHERE with a='foo' . This method is not recommended due to SQL injection vulnerabilities.

Answer 6

EDIT: As others have pointed out, you should not do anything special to pass an empty string to a parameterized query.

But anyway , if you use dynamic SQL queries in a query like:

 Declare sqlText VARCHAR(MAX) set sqlText = 'SELECT ....' EXEC(sqlText) 

then you will need to escape each of the single quotes as '''' to get the equivalent of an empty string. So, just 4 quotes. The first two single quotes will be equivalent to getting one quote at runtime. Two more single quotes will get the final quote at runtime.