CODEWITHSUNDEEP
javasqlescapingprepared-statement
Andrew
Andrew

Reputation: 169

Java PreparedStatement using two single quotes for empty string parameter

I am using a PreparedStatement with sql such as:

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 the resulting statement gets two single quotes. As in: VALUES ('foo','','') Then I get an exception since two single quotes is an escape sequence.

I can't believe I couldn't find anything on this through searching, but I could not. What is going on here?

Upvotes: 7

Views: 7440

Answers (6)

Sabir Khan
Sabir Khan

Reputation: 10142

You have not shared your Java Exception & that would have helped in formulating a precise answer.

There are two aspects in your program - Java & DB Constraints.

For Java, Empty String is "" ( Its double quotes not single quotes ) so as long as your Java references - a, b & c have this value when value is empty , I don't see any issue as far as PreparedStatement is concerned. You don't have to worry about single quote escape sequence if using PreparedStatement. I am not sure why you listing values as ['foo','',''] , it should be ["foo","",""].

Second aspect that your DB columns a, b & c might not allow empty values due to constraints is altogether a different thing and can't be controlled with Java code. DB Table Schema needs to be changed to allow default values in that case.

Discussion about single quote escape sequence is relevant when somebody is preparing SQL query by directly appending values in query string like , "insert into foo (a,b,c) values ('foo','','')"; or a WHERE clause with a='foo' . This way is not recommended though due to SQL Injection vulnerabilities.

Upvotes: 0

Ramesh Kumar
Ramesh Kumar

Reputation: 1

Replace single quote ' with UNICODE \u2019 value. For more details click on this url

Upvotes: 0

bluish
bluish

Reputation: 27400

As the OP doesn't do what @Adam suggested in the comments, I'll do it. It's useful for future readers. Thanks to @user119179 for the idea.

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

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

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

As in: VALUES ('foo','','') Then I get an exception since two single quotes is an escape sequence.

There is a misunderstanding here. The two single quotes is the empty string. There is no escape sequence happening. It is an escaped quote only if it is in another single quote. If you are getting an exception, it is probably elsewhere, such as a constraint on the column in the database.

The statement

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

is very valid SQL.

Upvotes: 2

Subhajit Roy
Subhajit Roy

Reputation: 123

@cyberkiwi is correct. May be your columns does not accept null values. What Exception are you getting ? If you have got any log reports then share it.

Upvotes: 0

peakit
peakit

Reputation: 29369

EDIT: As others pointed out, you should n't be doing anything special to pass empty string to a parametrized query.

But, just in case if you happen to be using Dynamic SQLs in a query like:

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

then you would need to escape each of the single quotes as '''' to get an equivalent to an empty string. So, a total of 4 quotes. The first 2 single quotes would be equivalent to getting a single quote at runtime. Another 2 single quotes will get the closing quote at runtime.

Upvotes: -2

Related Questions