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
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.Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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)
Method 2
Handle backslashes and all control characters in mysql load data infile tool:
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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b899f8">[email protected]</a>#$%^&*()_+=-[]|}{;'":/.?>,< anything but tabStep 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="452c217805262a2974">[email protected]</a>, <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="492a213c2a2227263b3b203a74092a26257b">[email protected]</a>;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 1Step 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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d5f495">[email protected]</a>#$%^&*()_+=-[]|}{;'":/.?>,< 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 nimitzn3 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="4f262b720f2c20237e">[email protected]</a>,
<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e1828994828a8f8e93938892dca1828e8dd3">[email protected]</a>;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0Step 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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="80a1c0">[email protected]</a>#$%^&*()_+=-[]|}{;'":/.?>,< 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.
Method 3
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"],"\");
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0