Re: disabling backslash as an escape character in strings
Yes, I use JDBC (with the MySQL Connector/J driver). I did not find a way in the JDBC API to escape a string before inserting it in a SQL string. The JDBC PreparedStatement class is able to escape parameters (in a database specific way) with the setString(parameterIndex, string) method, but it's designed for hard-coded insert/update queries. I use a custom function to generate queries that updates only fields that need to be updated, so I can't use PreparedStatement (unless I make my code hard to read/modify). The setString() method must be calling a MySQL specific escape function internally, but I don't know if there's something in JDBC to call that function directly or of it is private. Any ideas? Joshua J. Kugler wrote: Are you using a high level library such as Perl::DBI? If so, you should run all your strings the quote method. That will quote it properly for each database you connect to. If you are connecting to all the databases yourself using custom code, I would recommend you find some database neutral libraries and go from there. j- k- On Thursday 08 April 2004 01:37 pm, Christos Karras said something like: Is there a way to disable the use of the backslash as an escape character in strings? I need to use an application that's designed to work on any database server supporting ANSI SQL. When it generates SQL insert/update queries, it doesn't escape backslashes in strings, because the ANSI SQL standard doesn't require backslashes to be escaped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disabling backslash as an escape character in strings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Christos Karras wrote: Yes, I use JDBC (with the MySQL Connector/J driver). I did not find a way in the JDBC API to escape a string before inserting it in a SQL string. The JDBC PreparedStatement class is able to escape parameters (in a database specific way) with the setString(parameterIndex, string) method, but it's designed for hard-coded insert/update queries. I use a custom function to generate queries that updates only fields that need to be updated, so I can't use PreparedStatement (unless I make my code hard to read/modify). The setString() method must be calling a MySQL specific escape function internally, but I don't know if there's something in JDBC to call that function directly or of it is private. Any ideas? Christos, There is nothing public in the API that allows you to do this (although you could just go look at how it is done in PreparedStatement.setString() since the driver ships with the source). The JDBC API in general expects that you will build 'ad-hoc' queries with prepared statements, both for performance and security reasons (SQL injection). There are many 'clean' ways of using prepared statements for this, ranging from rolling your own and keeping track of when you need to append a string to your query, and replace it with a '?' instead, and go back and re-substitute all of your strings with .setString() from the list of subsitutions you've made, to using an ORM that has a query-builder API, like Hibernate's Criteria API that lets you build SQL in an object-oriented way, and takes care of all of this behind the scenes. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAdxBEtvXNTca6JD8RAijhAKCwS6gcIHrzHwGPEdzMMe30KfSmRgCfY0uK 5AyNbcLE/jKetZloIUg6vC0= =XfTH -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disabling backslash as an escape character in strings
Is there a way to disable the use of the backslash as an escape
character in strings? I need to use an application that's designed to
work on any database server supporting ANSI SQL. When it generates SQL
insert/update queries, it doesn't escape backslashes in strings, because
the ANSI SQL standard doesn't require backslashes to be escaped.
So to insert the value \, the application generates the following query:
INSERT INTO (test) VALUES('\');
Which causes an error in MySQL because it thinks the \ is an escape
character and the string is not closed.
If I modify the application to escape backslashes by replacing \ by \\,
it works with MySQL, but with other databases that don't interpret the
backslash as an escape character, it inserts two backslashes instead of one.
What could I do to tell MySQL it should interpret strings in the
standard way?
I tried starting mysqld in ANSI mode (mysqld-max-nt --ansi) but it
doesn't solve the problem.
I would also prefer a per-connection way to fix this, is there an option
I can set when connecting that won't affect other connections? I also
have other applications using the same MySQL server, some of which are
designed specifically for MySQL, so they may escape backslashes in the
MySQL way and switching the whole server to ANSI mode would break them.
I'm using MySQL 3.23 but I'm willing to upgrade to the latest 4.0x if it
can solve this problem.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disabling backslash as an escape character in strings
Are you using a high level library such as Perl::DBI? If so, you should run
all your strings the quote method. That will quote it properly for each
database you connect to. If you are connecting to all the databases yourself
using custom code, I would recommend you find some database neutral libraries
and go from there.
j- k-
On Thursday 08 April 2004 01:37 pm, Christos Karras said something like:
Is there a way to disable the use of the backslash as an escape
character in strings? I need to use an application that's designed to
work on any database server supporting ANSI SQL. When it generates SQL
insert/update queries, it doesn't escape backslashes in strings, because
the ANSI SQL standard doesn't require backslashes to be escaped.
So to insert the value \, the application generates the following query:
INSERT INTO (test) VALUES('\');
Which causes an error in MySQL because it thinks the \ is an escape
character and the string is not closed.
If I modify the application to escape backslashes by replacing \ by \\,
it works with MySQL, but with other databases that don't interpret the
backslash as an escape character, it inserts two backslashes instead of
one.
What could I do to tell MySQL it should interpret strings in the
standard way?
I tried starting mysqld in ANSI mode (mysqld-max-nt --ansi) but it
doesn't solve the problem.
I would also prefer a per-connection way to fix this, is there an option
I can set when connecting that won't affect other connections? I also
have other applications using the same MySQL server, some of which are
designed specifically for MySQL, so they may escape backslashes in the
MySQL way and switching the whole server to ANSI mode would break them.
I'm using MySQL 3.23 but I'm willing to upgrade to the latest 4.0x if it
can solve this problem.
--
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ... ..- .--.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and under
the earth, that Jesus Christ is LORD -- Count on it!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
escape character within sql statement.
Hi,
Following statement was working fine untill recently when someone entered specail
characters in insert statement.
eg
insert in db (col1,col2) values('a1','a2');
where a1=Here's
a2=Your's
thats is when apostrope is applied.
Thanks
Adamji
Re: escape character within sql statement.
Hi, where a1=Here's You need to escape the ' character with a backslash before you send the query to the server. If you develope with C this link might be useful: http://www.mysql.com/doc/en/mysql_real_escape_string.html If you develope wit perl + DBI there comes a quote methode with the DBI package - if I remember right - which escapes the characters which need escaping. How to use/escape strings properly is documented at this webside: http://www.mysql.com/doc/en/String_syntax.html best regards Stephan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
escape character
Hi can anybody tell me how can i insert rtf data in my sql, since it
contains escape characters
like this
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0
Verdana;}{\f1\fnil\fcharset2 Webdings;}{\f2\fnil\fcharset0 MS Sans Serif;}}
{\colortbl ;\red0\green0\blue0;\red0\green0\blue255;}
\viewkind4\uc1\pard\cf1\b\f0\fs20 SanjivnullKapila\b0\f1\fs24 4\cf2\f2\fs17
455
\par \pard }
So it deletes the characters \r and \b. Column type is text.
Thanks in advance
Regards
Daya Krishan Dubey
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: escape character
Daya,
Saturday, November 09, 2002, 2:28:07 PM, you wrote:
DKD Hi can anybody tell me how can i insert rtf data in my sql, since it
DKD contains escape characters
DKD like this
DKD {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0
DKD Verdana;}{\f1\fnil\fcharset2 Webdings;}{\f2\fnil\fcharset0 MS Sans Serif;}}
DKD {\colortbl ;\red0\green0\blue0;\red0\green0\blue255;}
DKD \viewkind4\uc1\pard\cf1\b\f0\fs20 SanjivnullKapila\b0\f1\fs24 4\cf2\f2\fs17
DKD 455
DKD \par \pard }
DKD So it deletes the characters \r and \b. Column type is text.
You need to escape characters:
http://www.mysql.com/doc/en/String_syntax.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: About escape character '\'
Sorry, I guess I answered too quickly. You have a problem, if you have to
use a
literal SQL statement, and the various DBMS's use different escape syntax.
However, in Java, you can just use a prepared statement
String sql = INSERT INTO files (filepath) VALUES (?));
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, c:\\Repository\\Pack\\);
ps.executeUpdate();
Hope that works for you.
Subject: RE: About escape character '\'
Date: Thu, 11 Apr 2002 13:54:46 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
sorry, I should say add 3 extra '\' and not just one in my previous
email, since if I add 3 more and it becomes:
INSERT INTO files (filepath) VALUES ('c:RepositoryPack')
which represents the following string in Java:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\')
(because in Java '\' is also an escape character)
So, in MySQL 'c:\Repository\Pack\' will be inserted, while in MS SQL and
Oracle 'c:\\Repository\\Pack\\' will be inserted and that's the problem
for me...
-Original Message-
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 7:27 AM
To: [EMAIL PROTECTED]
Cc: Kathy Sung
Subject: Re: About escape character '\'
Kathy,
You shouldn't have a problem here--it's Java, not MySQL, that requires
the
doubled '\' in a string literal.
In Java, the string literal:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
represents the string whose content is
INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
so what gets inserted is, in fact,
c:\Repository\Pack\
Subject: About escape character '\'
Date: Wed, 10 Apr 2002 19:44:21 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hi all,
I want to insert the string 'c:\Repository\Pack\' into a mysql table
using java and I did it as follows:
sql =3D3D INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\'
);
insertStmt.execute(sql);
I got an error and I know I should add an extra '\' to escape each of
the '\' in the above sql statement. But, the problem is MS SQL and
Oracle do not treat '\' as an escape character in sql statements, and
I
want to keep my Java program as database-independent as possible. (and
I
don't want the whole string 'c:\\Repository\\Pack\\' to be stored in
the
database when I use MS SQL server or Oracle)
Any suggestion to my problem will be greatly appreciated.
Thanks,
Kathy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: About escape character '\'
thanks a lot, it helps
Kathy
-Original Message-
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 12, 2002 9:44 AM
To: [EMAIL PROTECTED]
Cc: Kathy Sung
Subject: RE: About escape character '\'
Sorry, I guess I answered too quickly. You have a problem, if you have
to
use a
literal SQL statement, and the various DBMS's use different escape
syntax.
However, in Java, you can just use a prepared statement
String sql = INSERT INTO files (filepath) VALUES (?));
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, c:\\Repository\\Pack\\);
ps.executeUpdate();
Hope that works for you.
Subject: RE: About escape character '\'
Date: Thu, 11 Apr 2002 13:54:46 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
sorry, I should say add 3 extra '\' and not just one in my previous
email, since if I add 3 more and it becomes:
INSERT INTO files (filepath) VALUES ('c:RepositoryPack')
which represents the following string in Java:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\')
(because in Java '\' is also an escape character)
So, in MySQL 'c:\Repository\Pack\' will be inserted, while in MS SQL
and
Oracle 'c:\\Repository\\Pack\\' will be inserted and that's the
problem
for me...
-Original Message-
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 7:27 AM
To: [EMAIL PROTECTED]
Cc: Kathy Sung
Subject: Re: About escape character '\'
Kathy,
You shouldn't have a problem here--it's Java, not MySQL, that requires
the
doubled '\' in a string literal.
In Java, the string literal:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
represents the string whose content is
INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
so what gets inserted is, in fact,
c:\Repository\Pack\
Subject: About escape character '\'
Date: Wed, 10 Apr 2002 19:44:21 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hi all,
I want to insert the string 'c:\Repository\Pack\' into a mysql table
using java and I did it as follows:
sql =3D3D INSERT INTO files (filepath) VALUES
('c:\Repository\Pack\'
);
insertStmt.execute(sql);
I got an error and I know I should add an extra '\' to escape each
of
the '\' in the above sql statement. But, the problem is MS SQL and
Oracle do not treat '\' as an escape character in sql statements,
and
I
want to keep my Java program as database-independent as possible.
(and
I
don't want the whole string 'c:\\Repository\\Pack\\' to be stored in
the
database when I use MS SQL server or Oracle)
Any suggestion to my problem will be greatly appreciated.
Thanks,
Kathy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: About escape character '\'
Kathy,
You shouldn't have a problem here--it's Java, not MySQL, that requires the
doubled '\' in a string literal.
In Java, the string literal:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
represents the string whose content is
INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
so what gets inserted is, in fact,
c:\Repository\Pack\
Subject: About escape character '\'
Date: Wed, 10 Apr 2002 19:44:21 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hi all,
I want to insert the string 'c:\Repository\Pack\' into a mysql table
using java and I did it as follows:
sql =3D INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' );
insertStmt.execute(sql);
I got an error and I know I should add an extra '\' to escape each of
the '\' in the above sql statement. But, the problem is MS SQL and
Oracle do not treat '\' as an escape character in sql statements, and I
want to keep my Java program as database-independent as possible. (and I
don't want the whole string 'c:\\Repository\\Pack\\' to be stored in the
database when I use MS SQL server or Oracle)
Any suggestion to my problem will be greatly appreciated.
Thanks,
Kathy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: About escape character '\'
sorry, I should say add 3 extra '\' and not just one in my previous
email, since if I add 3 more and it becomes:
INSERT INTO files (filepath) VALUES ('c:RepositoryPack')
which represents the following string in Java:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\')
(because in Java '\' is also an escape character)
So, in MySQL 'c:\Repository\Pack\' will be inserted, while in MS SQL and
Oracle 'c:\\Repository\\Pack\\' will be inserted and that's the problem
for me...
-Original Message-
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 7:27 AM
To: [EMAIL PROTECTED]
Cc: Kathy Sung
Subject: Re: About escape character '\'
Kathy,
You shouldn't have a problem here--it's Java, not MySQL, that requires
the
doubled '\' in a string literal.
In Java, the string literal:
INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
represents the string whose content is
INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
so what gets inserted is, in fact,
c:\Repository\Pack\
Subject: About escape character '\'
Date: Wed, 10 Apr 2002 19:44:21 -0400
From: Kathy Sung [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hi all,
I want to insert the string 'c:\Repository\Pack\' into a mysql table
using java and I did it as follows:
sql =3D INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\'
);
insertStmt.execute(sql);
I got an error and I know I should add an extra '\' to escape each of
the '\' in the above sql statement. But, the problem is MS SQL and
Oracle do not treat '\' as an escape character in sql statements, and
I
want to keep my Java program as database-independent as possible. (and
I
don't want the whole string 'c:\\Repository\\Pack\\' to be stored in
the
database when I use MS SQL server or Oracle)
Any suggestion to my problem will be greatly appreciated.
Thanks,
Kathy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
About escape character '\'
Hi all,
I want to insert the string 'c:\Repository\Pack\' into a mysql table
using java and I did it as follows:
sql = INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' );
insertStmt.execute(sql);
I got an error and I know I should add an extra '\' to escape each of
the '\' in the above sql statement. But, the problem is MS SQL and
Oracle do not treat '\' as an escape character in sql statements, and I
want to keep my Java program as database-independent as possible. (and I
don't want the whole string 'c:\\Repository\\Pack\\' to be stored in the
database when I use MS SQL server or Oracle)
Any suggestion to my problem will be greatly appreciated.
Thanks,
Kathy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
escape character question
i, I need to know how to put email addresses into the mysql database without encountering the escape character. All of the email addresses that contain an underscore are receiving a backslash before the underscore. For example, [EMAIL PROTECTED] turns into mike\[EMAIL PROTECTED] This escape character feature is causing problems for me since I need to use the stored email addresses with a mailer program. Any help would be greatly appreciated. Thanks, Michael Dupey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
escape character for # sign?
How can I escape the mysql comment # character in sql statements? Thanks, Doug Sherman Emanuel.exe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
VIRUS FOUND Re: escape character for # sign?
Attention, the original message of Funky Gao and following replies contain the virus W32.Navidad.16896 Antonio
