Welcome to WebmasterWorld Guest from 107.20.75.63

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL and Quotation marks

     
4:45 pm on Jul 23, 2002 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 29, 2002
posts:72
votes: 0


I have a page on a client's website that is used to update comapany news.

There are a couple of PHP scripts to update or add news items. The problem is that any time there is a quotation mark in the text of the news item, MySQL interprets it as the end of the string and then ends up returning a syntax error.

Is there anyway to make MySQL ignore the quotation marks inside the query?

I know how to do this when I echo html, but it doesn't seem to work the same way for the MySQL query.

4:47 pm on July 23, 2002 (gmt 0)

Administrator from US 

WebmasterWorld Administrator brett_tabke is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Sept 21, 1999
posts:38047
votes: 11


Ya, you have to escape all sql queries. Not being the php person around here, not sure how you do that with php.
4:50 pm on July 23, 2002 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 29, 2002
posts:72
votes: 0


Would that be with magic quotes?

I've read a bit about these, but I'm not exactly sure how they work.

4:55 pm on July 23, 2002 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15755
votes: 0


if you just want to escape chars in a large string you can use addslashes() [php.net] and then when you output it you can use stripslashes() [php.net].
4:55 pm on July 23, 2002 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 29, 2002
posts:72
votes: 0


Nevermind I found the answer.

If anyone else was wondering. The quotes are escaped with the addslashes function.

4:56 pm on July 23, 2002 (gmt 0)

Junior Member

10+ Year Member

joined:June 3, 2002
posts:169
votes: 0


well you can use the following before you execute your insert:

$escaped_query = mysql_escape_string($query)
$result = mysql_query($escaped_query)

mavherick

4:58 pm on July 23, 2002 (gmt 0)

New User

10+ Year Member

joined:July 23, 2002
posts:11
votes: 0


A quick word on Magic Quotes. Although it can be very tempting to use, they can prove to be a nightmare on portability. I highly advise against it unless you have a particular reason that they are needed. Standardization is good - magic quotes are not standard. :) Stick with add/strip slashes.
5:02 pm on July 23, 2002 (gmt 0)

Preferred Member

10+ Year Member

joined:May 9, 2001
posts:416
votes: 0


You can test to see if magic quotes are on using get_magic_quotes_runtime (0 = off, 1 = on).

addslashes() and stripslashes() are the functions to use to escape double quotes et al.

see:
www.php.net/get_magic_quotes_runtime
www.php.net/addslashes
www.php.net/stripslashes

6:14 pm on July 23, 2002 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 4, 2001
posts:997
votes: 0


Another thing to note is that if you are taking any user variable and running a sql query with it, you will want to run addslashes first. Even if the input is done on a drop down box w/ two choices. Inputs to a script can always be changed by a malicious user. Imagine if your yes/no input got changed to the string '";DROP *;' where the double qoutes are actually part of the string. The ; would indicate the end of 1 mySql query and the beginning of the next one. While the first one may return invalid, the next one will delete the entire database (oh fun, huh?).
6:29 pm on July 23, 2002 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 29, 2002
posts:72
votes: 0


Thanks for the heads up on that. I will definitely use addslashes from this point on.