Re: Having trouble getting with a text string that has single quotes at start and end

[Steve McLeod]

I think you need to use just two single quotes at the beginning and end.

 Insert into PAYEES (NAME) values(''Starbucks'')

On Thursday, 26 July 2012 05:57:23 UTC+2, sandstones wrote:

I am dealing with some data that is a bit dirty, but I would still expect this to work.  I am trying to insert a text string that is 'Starbucks' WITH a single quote at the start and at the end.

The SQL statement I generate is
       Insert into PAYEES (NAME) values('''Starbucks''')
(its not that obvious in google's font, but there are 3 single quotes before and after Starbucks).  According to Oracle, that is the way they do it.

In H2 I getand  error of:
SQLState: 42001
Error Code: 42001
Message: Syntax error in SQL statement "INSERT INTO PAYEES (NAME) VALUES(NULL'StarbucksNULL'[*]) "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
Insert into PAYEES (NAME) values(NULL'StarbucksNULL') [42001-168]

Is there a way to do this, or do I need to start checking all the fields to clean up?
And who knows, someone might want the quote.

[Steve McLeod]

Oops, my mistake. Three quotes is right. This works for me:

drop table if exists foobar;

create table foobar (name varchar(20));

insert into foobar (name) values ('''steve''');

Furthermore, if this is being generated by Java code, then you shouldn't be doing it with string concatenation.

You should be doing this:

Not only does this let the JDBC driver take care of the work of escaping the single quotes at the beginning and end of the string, it is also safer against SQL Injection attacks.

 

            // assuming url contains the correct url for your H2 database...

            final Connection connection = DriverManager.getConnection(url);

            PreparedStatement statement = connection.prepareStatement("insert into foobar (name) values (?)");

            statement.setString(1, "'Steve'");

            statement.executeUpdate();

[Thomas Mueller]

Hi,

> The SQL statement I generate is
> Insert into PAYEES (NAME) values('''Starbucks''')

I'm quite sure that's not true. I believe the SQL statement you
generate is the one that is listed in the error message:

Insert into PAYEES (NAME) values(NULL'StarbucksNULL')

And obviously, this statement is illegal.

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/r4zIASxkZScJ.
>
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.