colon questions
Vishwas
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?
Lew
> i [sic] am createing a update string such as below
> "update __ values ( ' " + var1 +" ' ) ";
>
> but whenever the var1 contains something like this 'tom's ' , the
> use this command to update to the database.
>
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
con...@lewscanon.com
> i [sic] am createing a update string such as below
> "update __ values ( ' " + var1 +" ' ) ";
>
> but whenever the var1 contains something like this 'tom's ' , the
> 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/interactive/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
David Harper
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
Arne Vajhøj
I am pretty sure that this is a somewhat munged SQL statement, which
means that the ' is good.
Arne
Arne Vajhøj
> 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/interactive/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
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
>>> 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
Lew
> ... 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
Roedy Green
<vishwass...@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
Arne Vajhøj
> 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.
There is a 99.99% chance that it is wrong. But since the
original poster did not say what database he was using, then
I preferred just calling it "non standard" - some databases
has some weird syntaxes.
Arne