colon questions

Hi

i am createing a update string such as below
"update __ values ( ' " + var1 +" ' ) ";

but whenever the var1 contains something like this 'tom's ' , the
string contains the extra colon, and the string breaks when i try to
use this command to update to the database.

How can i bypass this?

Vishwas wrote:
> i [sic] am createing a update string such as below
> "update __ values ( ' " + var1 +" ' ) ";
>
> but whenever the var1 contains something like this 'tom's ' , the
> string contains the extra colon, and the string breaks when i [sic] try to
> use this command to update to the database.
>
> How can i [sic] bypass this?

In the first place, your question has nothing to do with databases. In the
second, the character ' is not a colon.

Colon is :

' is apostrophe, also called single quote.

Now let's look at the expression:

"update __ values ( ' " + var1 +" ' ) ";

Now let's make that a complete statement:

String s = "update __ values ( ' " + var1 + " ' ) ";

This produces no compiler errors (in a larger class definition with 'var1'
properly declared).

Your other example

'tom's '

or even

'tom'

would not work, because the single quote does not delimit Strings in the first
place. Single quotes delimit single characters, like

't'

To delimit a String you need the double quote "

"tom"
"t"

To indicate the single-quote character (as a character, not a String) you use
the backslash \

'\''

<http://java.sun.com/docs/books/jls/third_edition/html/lexical.html#3.10.6>

I recommend that you start reading here:

<http://java.sun.com/docs/books/tutorial/index.html>

--
Lew

On Jul 1, 2:40*am, Vishwas <vishwasshrikha...@gmail.com> wrote:
> i [sic] am createing a update string such as below
> "update __ values ( ' " + var1 +" ' ) ";
>
> but whenever the var1 contains something like this 'tom's ' , the
> string contains the extra colon, and the string breaks when i [sic] try to
> use this command to update to the database.

When you say "the string breaks" in a Java forum, I first look for
Java problems. On second look I see that you have SQL problems.

Any good SQL reference (RTFM) will tell you how to escape a single
quote, by repeating it.
<http://www.postgresql.org/docs/8.3/i...ve/sql-syntax-
lexical.html#SQL-SYNTAX-CONSTANTS>

> To include a single-quote character within a string constant,
> write two adjacent single quotes, e.g. 'Dianne''s horse'.

Since this is a Java forum, you should show complete, valid Java code
by way of example, and to avoid confusion.
<http://pscode.org/sscce.html>

public class Sqlizer
{
public static void main( String [] args )
{
String var1 = "Dianne''s horse";
String cmd = "UPDATE huxtable (descrip) VALUES ( '" + var1 + "' )";
// DO NOT EVER DO SQL IN THIS MANNER!
// IT IS A SECURITY HOLE BIG ENOUGH TO COAST A TRUCK THROUGH!
// USE PreparedStatement!
}
}

Note that two single-quote characters like this ''
are not the same as a single double-quote character "

--
Lew

Vishwas wrote:
> Hi
>
> i am createing a update string such as below
> "update __ values ( ' " + var1 +" ' ) ";
>
> but whenever the var1 contains something like this 'tom's ' , the
> string contains the extra colon, and the string breaks when i try to
> use this command to update to the database.
>
> How can i bypass this?

A widely recommended method to avoid problems with quotes is to use a
PreparedStatement:

Connection conn;
...
String sql = "update MYTABLE set name = ? where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
...
pstmt.setString(1, "tom's");
pstmt.setInt(2, 1234);
pstmt.executeUpdate();

The ? in the SQL string are called placeholders. They are numbered 1,
2, and so on.

The setString method call substitutes the string "tom's" in place of the
first ? in the SQL string, but it also alters the string "tom's" to
insert whatever quoting (backslashes to escape ", double '' to represent
a single ', etc ...) the database server requires.

You no longer have to worry about how to escape special characters such
as quotes in strings, and your code is portable -- it will work with
PostgreSQL as well as with MySQL or Oracle or any other database system.

David Harper
Cambridge, England

Lew wrote:
> Vishwas wrote:
>> i [sic] am createing a update string such as below
>> "update __ values ( ' " + var1 +" ' ) ";
>>
>> but whenever the var1 contains something like this 'tom's ' , the
>> string contains the extra colon, and the string breaks when i [sic]
>> try to
>> use this command to update to the database.
>>
>> How can i [sic] bypass this?
>
> In the first place, your question has nothing to do with databases. In
> the second, the character ' is not a colon.
>
> Colon is :
>
> ' is apostrophe, also called single quote.
>
> Now let's look at the expression:
>
> "update __ values ( ' " + var1 +" ' ) ";
>
> Now let's make that a complete statement:
>
> String s = "update __ values ( ' " + var1 + " ' ) ";
>
> This produces no compiler errors (in a larger class definition with
> 'var1' properly declared).
>
> Your other example
>
> 'tom's '
>
> or even
>
> 'tom'
>
> would not work, because the single quote does not delimit Strings in the
> first place. Single quotes delimit single characters, like
>
> 't'
>
> To delimit a String you need the double quote "
>
> "tom"
> "t"
>
> To indicate the single-quote character (as a character, not a String)
> you use the backslash \
>
> '\''
>
> <http://java.sun.com/docs/books/jls/third_edition/html/lexical.html#3.10.6>
>
> I recommend that you start reading here:
>
> <http://java.sun.com/docs/books/tutorial/index.html>

I am pretty sure that this is a somewhat munged SQL statement, which
means that the ' is good.

Arne

conrad@lewscanon.com wrote:
> On Jul 1, 2:40 am, Vishwas <vishwasshrikha...@gmail.com> wrote:
>> i [sic] am createing a update string such as below
>> "update __ values ( ' " + var1 +" ' ) ";
>>
>> but whenever the var1 contains something like this 'tom's ' , the
>> string contains the extra colon, and the string breaks when i [sic] try to
>> use this command to update to the database.
>
> When you say "the string breaks" in a Java forum, I first look for
> Java problems. On second look I see that you have SQL problems.

:-)

> Any good SQL reference (RTFM) will tell you how to escape a single
> quote, by repeating it.
> <http://www.postgresql.org/docs/8.3/i...ve/sql-syntax-
> lexical.html#SQL-SYNTAX-CONSTANTS>

But your subtle recommendation below is actually better.

> String var1 = "Dianne''s horse";
> String cmd = "UPDATE huxtable (descrip) VALUES ( '" + var1 + "' )";
> // DO NOT EVER DO SQL IN THIS MANNER!
> // IT IS A SECURITY HOLE BIG ENOUGH TO COAST A TRUCK THROUGH!
> // USE PreparedStatement!

Arne

Arne Vajhøj wrote:
> conrad@lewscanon.com wrote:
>> On Jul 1, 2:40 am, Vishwas <vishwasshrikha...@gmail.com> wrote:
>>> i [sic] am createing a update string such as below
>>> "update __ values ( ' " + var1 +" ' ) ";
>>>
>>> but whenever the var1 contains something like this 'tom's ' , the
>>> string contains the extra colon, and the string breaks when i [sic]
>>> try to
>>> use this command to update to the database.
>>
>> When you say "the string breaks" in a Java forum, I first look for
>> Java problems. On second look I see that you have SQL problems.
>
> :-)
>
>> Any good SQL reference (RTFM) will tell you how to escape a single
>> quote, by repeating it.
>> <http://www.postgresql.org/docs/8.3/i...ve/sql-syntax-
>> lexical.html#SQL-SYNTAX-CONSTANTS>
>
> But your subtle recommendation below is actually better.
>
>> String var1 = "Dianne''s horse";
>> String cmd = "UPDATE huxtable (descrip) VALUES ( '" + var1 + "' )";
>> // DO NOT EVER DO SQL IN THIS MANNER!
>> // IT IS A SECURITY HOLE BIG ENOUGH TO COAST A TRUCK THROUGH!
>> // USE PreparedStatement!

Besides the SQL looks very non standard. The standard is:

INSERT INTO tbl VALUES(val);
INSERT INTO tbl(fld) VALUES(val);
UPDATE tbl SET fld=val;
UPDATE tbl SET fld=val WHERE id=otherval;

Arne

Lew wrote:
>>> String var1 = "Dianne''s horse";
>>> String cmd = "UPDATE huxtable (descrip) VALUES ( '" + var1 + "' )";
>>> // DO NOT EVER DO SQL IN THIS MANNER!
>>> // IT IS A SECURITY HOLE BIG ENOUGH TO COAST A TRUCK THROUGH!
>>> // USE PreparedStatement!

Arne Vajhøj wrote:
> Besides the SQL looks very non standard. The standard is:
>
> INSERT INTO tbl VALUES(val);
> INSERT INTO tbl(fld) VALUES(val);
> UPDATE tbl SET fld=val;
> UPDATE tbl SET fld=val WHERE id=otherval;

Actually, it's wrong, not non-standard. I mistakenly didn't check it.

Not that mattered for my points, which were mostly about Java. My advice not
to do SQL that way is also valid, for valid SQL, despite the fact that my SQL
was not valid.

So whichever of the INSERT INTO (which is what I *meant* to write) or UPDATE
commands one uses, one should not inject Strings directly into the statement
but use PreparedStatement instead.

--
Lew

David Harper wrote:
> ... and your code is portable -- it will work with
> PostgreSQL as well as with MySQL or Oracle or any other database system.

That last part is only true to the extent one uses portable SQL, which one
cannot always do much in real life. For example, datetime types are radically
different with radically different semantics between the RDBMSes you
mentioned. You will not get the same behavior with, say,
ResultSet#getTimestamp() across all three.

--
Lew

On Mon, 30 Jun 2008 23:40:27 -0700 (PDT), Vishwas
<vishwasshrikhande@gmail.com> wrote, quoted or indirectly quoted
someone who said :

> like this 'tom's '

see http://mindprod.com/jgloss/jdbc.html#LITERALS
--

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com