Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-21
4
Medium Priority
?
2,332 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:stockblocks
  • 2
  • 2
4 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 2000 total points
ID: 16247888
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
 

Author Comment

by:stockblocks
ID: 16251182
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
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16251407
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
 

Author Comment

by:stockblocks
ID: 16252076
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question