MySQL INSERT with pound sign (#) in one of the fields is failing, and escaping with '\' does not work.

I'm trying to issue a simple set of SQL instructions to create/populate a table in MySQL via phpMyAdmin.  One of the fields has a pound sign in it, and I don't know how to escape it. ("\" isn't working).

Here are the versions reported...

  phpMyAdmin 2.1.0
  MySQL 3.22.25

Here's my SQL...

# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: localhost Database : TESTDB
# --------------------------------------------------------

#
# Table structure for table 'testtable'
#

CREATE TABLE testtable (
   testID int(11) DEFAULT '0' NOT NULL auto_increment,
   testName varchar(40) NOT NULL,
   testAddr varchar(50) NOT NULL,
   testCity varchar(30) NOT NULL,
   testState varchar(10) NOT NULL,
   testZip varchar(10) NOT NULL,
   PRIMARY KEY (testID)
);

#
# Dumping data for table 'testtable'
#

INSERT INTO testtable VALUES ( '100', 'John Doe\'s House',  '123 Main St.', 'Anytown', 'AK', '99901');
INSERT INTO testtable VALUES ( '101', 'Art Stowe', 'RR #1', 'GRock', 'NJ', '07452');
INSERT INTO testtable VALUES ( '102', 'Jane Doe',  '456 Maple Ave.', 'Othertown', 'AK', '99901');


Upon submitting the above, MySQL/phpMyAdmin said: You have an error in your SQL syntax near '102', 'Jane Doe', '456 Maple Ave.', 'Othertown', 'AK', '99901');' at line 1

As I stated above, I discovered it was the pound sign (this makes sense, MySQL thinks I'm beginning a comment). So I tried escaping it with '\' but that did not work.  For kicks, I even tried '##'... but same failure.

So I removed the pound sign and resubmitted the SQL into phpMyAdmin and the table was successfully created with all three entries.  I then used phpMyAdmin to edit the Art Stowe address from "RR 1" back to "RR #1".  I then used phpMyAdmin's "View dump (schema) of table" function to dump the structure and data.  This way, I could see how it deals with the pound sign.  But here's what it dumped...


# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: localhost Database : TESTDB
# --------------------------------------------------------

#
# Table structure for table 'testtable'
#

CREATE TABLE testtable (
   testID int(11) DEFAULT '0' NOT NULL auto_increment,
   testName varchar(40) NOT NULL,
   testAddr varchar(50) NOT NULL,
   testCity varchar(30) NOT NULL,
   testState varchar(10) NOT NULL,
   testZip varchar(10) NOT NULL,
   PRIMARY KEY (testID)
);

#
# Dumping data for table 'testtable'
#

INSERT INTO testtable VALUES ( '100', 'John Doe\'s House', '123 Main St.', 'Anytown', 'AK', '99901');
INSERT INTO testtable VALUES ( '101', 'Art Stowe', 'RR #1', 'GRock', 'NJ', '07452');
INSERT INTO testtable VALUES ( '102', 'Jane Doe', '456 Maple Ave.', 'Othertown', 'AK', '99901');

From observation, you will see this is identical SQL code that I dealt with originally. It did not escape the pound sign.  This makes no sense to me.

Any tips on how to get MySQL/phpMyAdmin to accept pound signs in an INSERT command?  To repeat, preceding the '#' with a '\' did not work.

Thanks,

SV

stockblocksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
Seems to be a phpMyAdmin bug - using the mysql command-line client it works fine:

mysql> CREATE TABLE testtable (
    ->    testID int(11) NOT NULL auto_increment,
    ->    testName varchar(40) NOT NULL,
    ->    testAddr varchar(50) NOT NULL,
    ->    testCity varchar(30) NOT NULL,
    ->    testState varchar(10) NOT NULL,
    ->    testZip varchar(10) NOT NULL,
    ->    PRIMARY KEY (testID)
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql>
mysql> #
mysql> # Dumping data for table 'testtable'
mysql> #
mysql>
mysql> INSERT INTO testtable VALUES ( '100', 'John Doe\'s House',  '123 Main St.
', 'Anytown', 'AK', '99901');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO testtable VALUES ( '101', 'Art Stowe', 'RR #1', 'GRock', 'NJ'
, '07452');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO testtable VALUES ( '102', 'Jane Doe',  '456 Maple Ave.', 'Oth
ertown', 'AK', '99901');
Query OK, 1 row affected (0.05 sec)

mysql> select * from testtable;
+--------+------------------+----------------+-----------+-----------+---------+

| testID | testName         | testAddr       | testCity  | testState | testZip |

+--------+------------------+----------------+-----------+-----------+---------+

|    100 | John Doe's House | 123 Main St.   | Anytown   | AK        | 99901   |

|    101 | Art Stowe        | RR #1          | GRock     | NJ        | 07452   |

|    102 | Jane Doe         | 456 Maple Ave. | Othertown | AK        | 99901   |

+--------+------------------+----------------+-----------+-----------+---------+

3 rows in set (0.02 sec)

mysql>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stockblocksAuthor Commented:
Thanks for the verification (that I'm doing nothing wrong).  I moved my test to another server with a more up-to-date phpMyAdmin environment, and the problem did not resurface.

SV
0
todd_farmerCommented:
Cool.  I tried to track information down about any relevent phpMyAdmin bugs, but my search didn't turn up anything useful.  For documentation for future readers of this post, do you mind indicating which version you updated to that solved the problem?

Thanks - and thanks for the points!
0
stockblocksAuthor Commented:
I didn't upgrade.  Rather, I simply ran the test on another one of my web sites (hosted by another provider).

I'm not a system admin-type, so upgrading server-hosted stuff -- such as phpMyAdmin --  is a task I take for granted!  (I.e., that's something I presume my web provider should do).  Thanks to your original response, I've learned that the first platform I tested it on is outdated/buggy, so I'll let the web provider know.

I hope this answers your question.  Thanks again,

SV
   
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.