Problem accessing data with \ in it

[zMisc]

I have a problem selecting records with \ in any values.

For example:

SELECT * FROM ADDRESS WHERE STREET = "A\"

will give an error:

If I replace \ with \\ then it works. Eg.

SELECT * FROM ADDRESS WHERE STREET = "A\\"

Is there any setting in MySQL that can overcome this?

Tks
John

[Bill Karwin]

"zMisc" <yo********@hot mail.com> wrote in message
news:lg******** *********@news-server.bigpond. net.au...

If I replace \ with \\ then it works. Eg.

SELECT * FROM ADDRESS WHERE STREET = "A\\"

Is there any setting in MySQL that can overcome this?

What do you mean overcome this? It's a feature of the string literal
syntax, very similar to handling of backslashes within strings in C/C++,
Java, PHP, or Perl.

There seem to be two alternatives, according to
http://dev.mysql.com/doc/refman/5.0/...g-syntax.html:

- Process the string with a function that escapes special characters, e.g.
mysql_real_esca pe_string() C API or PHP API functions, or quote() in Perl
DBI. These API functions scan for characters that need the backslash
treatment (like \ or ') and insert the backslashes as needed.

- Use parameterized queries. Prepare a query "SELECT * FROM ADDRESS WHERE
STREET = ?" and then execute the query from your application with a
parameter set to the string "A\". No escaping is necessary when you do this
(except what is required in your application programming language if you
specify the string as a literal).

Regards,
Bill K.

[zMisc]

Hi Bill,

As usuall thank you for your response.

I replace \ with \\ before the update and it works.

The problem is I am converting my app that supports MS Access and MS SQL to
now support MySQL so I would prefer not to change all my select to use
parameterized queries.

Access and MS SQL can handle the \ character.

Tks
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dt******** @enews1.newsguy .com...

"zMisc" <yo********@hot mail.com> wrote in message
news:lg******** *********@news-server.bigpond. net.au...

If I replace \ with \\ then it works. Eg.

SELECT * FROM ADDRESS WHERE STREET = "A\\"

Is there any setting in MySQL that can overcome this?

What do you mean overcome this? It's a feature of the string literal
syntax, very similar to handling of backslashes within strings in C/C++,
Java, PHP, or Perl.

There seem to be two alternatives, according to
http://dev.mysql.com/doc/refman/5.0/...g-syntax.html:

- Process the string with a function that escapes special characters, e.g.
mysql_real_esca pe_string() C API or PHP API functions, or quote() in Perl
DBI. These API functions scan for characters that need the backslash
treatment (like \ or ') and insert the backslashes as needed.

- Use parameterized queries. Prepare a query "SELECT * FROM ADDRESS WHERE
STREET = ?" and then execute the query from your application with a
parameter set to the string "A\". No escaping is necessary when you do
this (except what is required in your application programming language if
you specify the string as a literal).

Regards,
Bill K.

[Bill Karwin]

"zMisc" <yo********@hot mail.com> wrote in message
news:9m******** **********@news-server.bigpond. net.au...

Access and MS SQL can handle the \ character.

Okay, I've found an option for you.
MySQL 5.0.3 and later implement a SQL mode called NO_BACKSLASH_ES CAPES.

See http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html for docs on
SQL modes and how to set them at runtime or when the MySQL server starts up.
You might also be interested in the mode MSSQL, to give greater
compatibility with MS SQL Server syntax.

NB: if you use MySQL version prior to 5.0.3, this option is not available.

Regards,
Bill K.

[zMisc]

Tks Bill.

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dt******** @enews2.newsguy .com...

"zMisc" <yo********@hot mail.com> wrote in message
news:9m******** **********@news-server.bigpond. net.au...

Access and MS SQL can handle the \ character.

Okay, I've found an option for you.
MySQL 5.0.3 and later implement a SQL mode called NO_BACKSLASH_ES CAPES.

See http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html for docs
on SQL modes and how to set them at runtime or when the MySQL server
starts up. You might also be interested in the mode MSSQL, to give greater
compatibility with MS SQL Server syntax.

NB: if you use MySQL version prior to 5.0.3, this option is not available.

Regards,
Bill K.

[zMisc]

Hi Bill,

Where can I find the MySQL version? I look at MySQL.exe and there's no
version property.

Tks

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dt******** @enews2.newsguy .com...

"zMisc" <yo********@hot mail.com> wrote in message
news:9m******** **********@news-server.bigpond. net.au...

Access and MS SQL can handle the \ character.

Okay, I've found an option for you.
MySQL 5.0.3 and later implement a SQL mode called NO_BACKSLASH_ES CAPES.

See http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html for docs
on SQL modes and how to set them at runtime or when the MySQL server
starts up. You might also be interested in the mode MSSQL, to give greater
compatibility with MS SQL Server syntax.

NB: if you use MySQL version prior to 5.0.3, this option is not available.

Regards,
Bill K.

[Bill Karwin]

"zMisc" <yo********@hot mail.com> wrote in message
news:va******** **********@news-server.bigpond. net.au...

Where can I find the MySQL version? I look at MySQL.exe and there's no
version property.

To get the MySQL server version (which is more relevant in this case),
connect to the MySQL server and issue a statement:
SHOW VARIABLES LIKE 'version';

The get the MySQL client version, you can run the command
"mysql.exe --version".

I make the distinction because the version of the client and the version of
the server are not necessarily the same, especially if you're using the
client to connect to a MySQL server instance running on another machine.

Regards,
Bill K.

[zMisc]

Hi Bill,

Tks.

I've tried setting:

set sql_mode = 'NO_BACKSLASH_E SCAPES'

then try this:

SELECT * FROM `TEST` WHERE `ADDRESS` = '1\10'

and get a syntax error. But:

SELECT * FROM `TEST` WHERE `ADDRESS` = '1\\10'

still works. It looks like set sql_mode = 'NO_BACKSLASH_E SCAPES' makes no
difference.

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dt******** *@enews1.newsgu y.com...

"zMisc" <yo********@hot mail.com> wrote in message
news:va******** **********@news-server.bigpond. net.au...

Where can I find the MySQL version? I look at MySQL.exe and there's no
version property.

To get the MySQL server version (which is more relevant in this case),
connect to the MySQL server and issue a statement:
SHOW VARIABLES LIKE 'version';

The get the MySQL client version, you can run the command
"mysql.exe --version".

I make the distinction because the version of the client and the version
of the server are not necessarily the same, especially if you're using the
client to connect to a MySQL server instance running on another machine.

Regards,
Bill K.

[Bill Karwin]

"zMisc" <yo********@hot mail.com> wrote in message
news:Of******** **********@news-server.bigpond. net.au...

It looks like set sql_mode = 'NO_BACKSLASH_E SCAPES' makes no difference.

I tried that too, and like you said, it didn't seem to have any effect.

I got it to work with:
set global sql_mode='NO_BA CKSLASH_ESCAPES ';
select 'foo\\bar'';

Gives: foo\\bar whereas that query gave foo\bar before.

Regards,
Bill K.

[Michael Austin]

zMisc wrote:

I have a problem selecting records with \ in any values.

For example:

SELECT * FROM ADDRESS WHERE STREET = "A\"

will give an error:

If I replace \ with \\ then it works. Eg.

SELECT * FROM ADDRESS WHERE STREET = "A\\"

Is there any setting in MySQL that can overcome this?

Tks
John

As an ANSI standard the default should be NO_BACKSLASH_ES CAPES. Anything
contained with the quotes 'xx\x\x\x' should be treated as data and NEVER
interpreted to be anything else. So, whoever the orginal designers were spent
way too much time working on **IX\Windows crap OS's.

IMHO Another flaw in MySQL.

Data is Data and should never to be treated the same as you would a variable in
an OS-level scripting language.

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)