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********@hotmail.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_escape_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.com> wrote in message
news:dt********@enews1.newsguy.com...

"zMisc" <yo********@hotmail.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_escape_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********@hotmail.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_ESCAPES.

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.com> wrote in message
news:dt********@enews2.newsguy.com...

"zMisc" <yo********@hotmail.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_ESCAPES.

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.com> wrote in message
news:dt********@enews2.newsguy.com...

"zMisc" <yo********@hotmail.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_ESCAPES.

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********@hotmail.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_ESCAPES'

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_ESCAPES' makes no
difference.

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

"zMisc" <yo********@hotmail.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********@hotmail.com> wrote in message
news:Of******************@news-server.bigpond.net.au...

It looks like set sql_mode = 'NO_BACKSLASH_ESCAPES' 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_BACKSLASH_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_ESCAPES. 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
:)

[Bill Karwin]

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:lm*****************@newssvr25.news.prodigy.ne t...

IMHO Another flaw in MySQL.

I agree that this fails to comply with the ANSI standard, but the lesser
evil is to avoid breaking people's existing code, when possible.

Regards,
Bill K.

[Michael Austin]

Bill Karwin wrote:

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:lm*****************@newssvr25.news.prodigy.ne t...

IMHO Another flaw in MySQL.

I agree that this fails to comply with the ANSI standard, but the lesser
evil is to avoid breaking people's existing code, when possible.

The real issue is that if the developers of MySQL had bothered to read the
standards and follow every other database company in the world in regards to
this issue, there would be no "existing" code to worry about because it would
have been done right in the first place.

Regards,
Bill K.

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

[Bill Karwin]

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:V7*****************@newssvr25.news.prodigy.ne t...

The real issue is that if the developers of MySQL had bothered to read the
standards and follow every other database company in the world in regards
to this issue, there would be no "existing" code to worry about because it
would have been done right in the first place.

So you're saying that no other RDBMS implementation uses backslash escape
sequences for special characters in string literals? I'm pretty sure that's
demonstrably untrue.

The escape sequence in SQL string literals is a very useful extension that
allows a string to contain special characters. Otherwise, we have to resort
to gymnastics of concatenation string expressions, UDF's, cartesian product
with a table containing a single "\n" character, etc.

There's a reason that virtually every programming language does define
string escape sequences. It's a pity that the ANSI SQL standard does not.

Regards,
Bill K.