Reputation: 1055
In MySQL, when I try to insert a backslash into my table, it does not accept it and gives me the content without the backslash.
id
is set to auto increment:
Code:
INSERT INTO gender (sex, date) VALUES (
'male are allowed \ female are not allowed', "2012-10-06")
How do I insert a literal backslash?
Notes about escape sequences:
Escape Sequence Character Represented by Sequence
\0 An ASCII NUL (0x00) character.
\' A single quote (“'”) character.
\" A double quote (“"”) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control+Z). See note following the table.
\\ A backslash (“\”) character.
\% A “%” character. See note following the table.
\_ A “_” character. See note following the table.
Upvotes: 28
Views: 69799
Reputation: 1
Just in case it helps someone, as I was struggling for a little while with this. If you are creating your MySQL string in PHP you will need to quadruple escape your backslash, i.e. have 4 backslashes to create two in your SQL statement.
e.g. php
$sqlquery="insert into images (image_path) values (\"\\\\images\\\\cars\\\\sportscars\\\\" . $image_name . ".jpg\");";
Upvotes: 0
Reputation: 153872
Step 1, create your table:
mysql> create table penguin (id int primary key, chucknorris VARCHAR(4000));
Query OK, 0 rows affected (0.01 sec)
Step 2, create your file to import and put this data in there.
1 spacealiens are on route
2 scramble the nimitz\
3 \its species 8472
4 \\\\\\\\\\\\\\\\\\
5 Bonus characters:!@#$%^&*()_+=-[]\|}{;'":/.?>,< anything but tab
Step 3, insert into your table:
mysql> load data local infile '/home/el/foo/textfile.txt' into table penguin
fields terminated by '\t' lines terminated by '\n'
(@col1, @col2) set id=@col1, chucknorris=@col2;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 1
Step 4, and of course, it causes this strange output:
mysql> select * from penguin;
+----+-----------------------------------------------------------------+
| id | chucknorris |
+----+-----------------------------------------------------------------+
| 1 | spacealiens are on route |
| 2 | scramble the nimitz |
| 3 | |
| 4 | \\\\\\\\\ |
| 5 | Bonus characters:!@#$%^&*()_+=-[]|}{;'":/.?>,< anything but tab |
+----+-----------------------------------------------------------------+
Step 5, analyze the warning:
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------- ------------------+
| Warning | 1262 | Row 2 was truncated; it contained more data than there |
| | | were input columns |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
Step 6, think about exactly what went wrong:
The backslash to the left of nimitz
caused the mysql load data parser to concatenate the end of line 2 with the beginning of line 3. Then it bumped up against a tab and put 'scramble the nimitz\n3 into row 2.
The rest of row 3 is skipped because the extra words its species 8472
do not fit anywhere, it produces the warning you see above.
Row 4 had 18 backslashes, so there is no problem, and shows up as 9 backslahes because each was escaped. Had there been an odd number, the error on row 2 would have happened to row 4.
The bonus characters on row 5 came through normally. Everything is allowed except tab.
Step 7, reset table penguin:
mysql> delete from penguin;
Step 8, load into your table with the fields escaped by
clause:
mysql> load data local infile '/home/el/foo/textfile.txt' into table penguin
fields terminated by '\t' escaped by '\b'
lines terminated by '\n' (@col1, @col2) set id=@col1,
chucknorris=@col2;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
Step 9, select from your table, interpret the results:
mysql> select * from penguin;
+----+------------------------------------------------------------------+
| id | chucknorris |
+----+------------------------------------------------------------------+
| 1 | spacealiens are on route |
| 2 | scramble the nimitz\ |
| 3 | \its species 8472 |
| 4 | \\\\\\\\\\\\\\\\\\ |
| 5 | Bonus characters:!@#$%^&*()_+=-[]\|}{;'":/.?>,< anything but tab |
+----+------------------------------------------------------------------+
5 rows in set (0.00 sec)
And now everything is as we expect. The backslash at the end of line 2 does not escape the newline. The backslash before i
on row 3 doesn't do anything. The 18 backslashes on row 4 are not escaped. And the bonus characters come through ok.
Upvotes: 9
Reputation: 17
you can use this code :
$yourVariable = addcslashes($_POST["your param"],"\\");
for example in my web form i want insert local directory :
$localAddress = addcslashes($_POST["localAddress"],"\\");
Upvotes: 2
Reputation: 382122
You need to escape your backslash :
INSERT INTO gender
(sex, date) VALUES (
'male are allowed \\ female are not allowed',
"2012-10-06")
Reference (with the list of all characters you must escape for mysql)
Upvotes: 35