MySQLDump issue with a special characters

Question

Code: use test;

CREATE TABLE Test_user(Username VARCHAR(15),path VARCHAR(100));

INSERT INTO Test_user VALUES('new1','C:\newfolder\one.xlsx'); INSERT INTO Test_user VALUES('new1','C:/newfolder/one.xlsx');

I have created a .sql file using MySQLDump, which has contents

Filename : Table1.sql

DROP TABLE IF EXISTS test_user; /*!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; CREATE TABLE test_user ( Username varchar(15) DEFAULT NULL, path varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES test_user WRITE; /*!40000 ALTER TABLE test_user DISABLE KEYS /; INSERT INTO test_user VALUES ('new1','C:\newfolderone.xlsx'),('new1','C:/newfolder/one.xlsx'); /!40000 ALTER TABLE test_user ENABLE KEYS */; UNLOCK TABLES;

in the above Table1.sql file ('new1','C:\newfolderone.xlsx') backslash before one.xlsx is not displaying , While trying to execute this file, i am getting output like this

new1 C:ewfolderone.xlsx new2 C:/newfolder/one.xlsx

Where it is not displaying \ at all , suggest me how to resolve it and also what are special characters like this backslash will create this kind of bug.

Thanks in advance..

Answer 1

You should read about string literals in MySQL.

There you’ll learn that a backslash character is used to escape some special strings, and to have literal backslashes in your strings (as in Windows pathnames), you have to double them:

INSERT INTO test_user VALUES ('new1','C:\\newfolderone.xlsx');

The complete list of characters in need of a special treatment is at the above link.

However, it is best to have the intermediate language deal with such problems. For instance, in PHP you would call PDO::quote; other platforms and languages provide similar facilities.