Re: how to store quotes in mysql?
thanks for your response. I'm already working on the php part but just wondered how bad is to have backslash in front of quotes. It's bad in the sense that you will have to predict every single event (character) from the input and act accordingly. The API writers had predicted them all and fixed bugs on it and you'd have to go through the same work, which is not good. (reinventing the wheel). always look for a stable library before doing it yourself ;) --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to store quotes in mysql?
hi to all. battling this problem on several forums and mailing lists, I got confused: when store string that contains quotations (ie afan's php shop) in mysql does it have to be stored with backslashes (afan\'s \php\ shop) or just the way it is? my login's telling me the way it is. am I wrong? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to store quotes in mysql?
On Wednesday 09 August 2006 07:49 am, [EMAIL PROTECTED] wrote:
hi to all.
battling this problem on several forums and mailing lists, I got confused:
when store string that contains quotations (ie afan's php shop) in mysql
does it have to be stored with backslashes (afan\'s \php\ shop) or just
the way it is? my login's telling me the way it is. am I wrong?
Yes, MySQL stores it that way for a specific reason. That is strings are
generally input in the form:
INSERT INTO table (blah) VALUES('blah');
That said, if you didn't have the slash escape, you'd have something like:
INSERT INTO table (blah) VALUES('I'm blah);
Which MySQL would choke on, not knowing what to do with m blah. Also, this is
done to prevent SQL injection, like:
INSERT INTO table (blah) VALUES('[bl' ; DELETE FROM table; SELECT('ah]');
where [] is what the user inputs. Now when displaying, you'll have to
unescape the slashes generally. Unfortunately I can't remember in PHP if
that's because of magic quotes or just the way the db has it stored. My gut
instinct is the former.
thanks for any help.
-afan
--
Chris White
PHP Programmer/DBaboon
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to store quotes in mysql?
battling this problem on several forums and mailing lists, I got
confused:
when store string that contains quotations (ie afan's php shop) in
mysql
does it have to be stored with backslashes (afan\'s \php\ shop) or
just
the way it is? my login's telling me the way it is. am I wrong?
Yes, MySQL stores it that way for a specific reason. That is strings
are
generally input in the form:
INSERT INTO table (blah) VALUES('blah');
That said, if you didn't have the slash escape, you'd have something
like:
INSERT INTO table (blah) VALUES('I'm blah);
Which MySQL would choke on, not knowing what to do with m blah. Also,
this is
done to prevent SQL injection, like:
INSERT INTO table (blah) VALUES('[bl' ; DELETE FROM table;
SELECT('ah]');
where [] is what the user inputs. Now when displaying, you'll have to
unescape the slashes generally. Unfortunately I can't remember in PHP
if
that's because of magic quotes or just the way the db has it stored.
My
gut
instinct is the former.
No no no no. If you have to unescape your data before you display it,
then you escaped it too many times.
If you have the following query:
INSERT INTO myTable VALUES ('I\'m Happy');
MySQL does NOT store this value as
I\'m Happy
it is stored as
I'm Happy
The escape character is needed so that the interpreter that reads your
SQL commands can understand the query. MySQL does not choke on
apostrophes or quotes. It has no problem with them. They're just
characters. The interpreter needs to know the difference between a
string-defining quote and a quote that is part of the data. Hence the
escape characters. Just as if you have the following PHP code:
?php
echo 'I\'m Happy';
?
PHP does not print out
I\'m Happy
it prints out
I'm Happy
The problem is indeed caused by magic quotes. Magic quotes
automagically escapes data for you. When you escape your escaped data
with addslashes or the like, you're escaping your escape characters as
well.
kgt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to store quotes in mysql?
On Wednesday 09 August 2006 08:37 am, Kristen G. Thorson wrote: [ stuff here ] kgt I'm confused.. did you read my email? Most of what you said doesn't seem to correlate with what I said. Can you quote the specific lines that you're disagreeing with? -- Chris White PHP Programmer/DBacillus Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to store quotes in mysql?
-Original Message-
From: Chris White [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 09, 2006 11:43 AM
To: mysql@lists.mysql.com
Subject: Re: how to store quotes in mysql?
On Wednesday 09 August 2006 08:37 am, Kristen G. Thorson wrote:
[ stuff here ]
kgt
I'm confused.. did you read my email? Most of what you said doesn't
seem
to
correlate with what I said. Can you quote the specific lines that
you're
disagreeing with?
--
Chris White
PHP Programmer/DBacillus
Interfuel
Sure:
Yes, MySQL stores it that way for a specific reason.
MySQL does not STORE escape characters, unless you have escaped an
escape character. The following query:
INSERT INTO myTABLE VALUES ('I\'m Happy');
is instructing MySQL to store the string value I'm Happy. It does not
store I\'m Happy.
Now when displaying, you'll have to unescape the slashes generally.
A clear indication that you have over-escaped your data. If you have
properly escaped your queries, then you should never need to unescape
them.
If
SELECT string_value FROM myTable;
returns
I\'m Happy
then you have inserted your data as follows:
INSERT INTO myTable VALUES( 'I\\\'m Happy' );
This happens because magic quotes produced escaped data:
I\'m Happy
You then run addslashes() or similar and end up with this:
I\\\' Happy
kgt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to store quotes in mysql?
[EMAIL PROTECTED] wrote: hi to all. battling this problem on several forums and mailing lists, I got confused: when store string that contains quotations (ie afan's php shop) in mysql does it have to be stored with backslashes (afan\'s \php\ shop) or just the way it is? my login's telling me the way it is. am I wrong? most programming languages have specific functions to scape properly all strings and also most of them to protect you from SQL injection (PHP is one example). You should search your language's documentation on how to prepare your queries passing arguments instead of concatenating it your self on the query and how to quote properly the strings so you can stop worrying about it once and for all... ;) cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to store quotes in mysql?
thanks for your response. I'm already working on the php part but just wondered how bad is to have backslash in front of quotes. thanks. -afan [EMAIL PROTECTED] wrote: hi to all. battling this problem on several forums and mailing lists, I got confused: when store string that contains quotations (ie afan's php shop) in mysql does it have to be stored with backslashes (afan\'s \php\ shop) or just the way it is? my login's telling me the way it is. am I wrong? most programming languages have specific functions to scape properly all strings and also most of them to protect you from SQL injection (PHP is one example). You should search your language's documentation on how to prepare your queries passing arguments instead of concatenating it your self on the query and how to quote properly the strings so you can stop worrying about it once and for all... ;) cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
