Geek Answers Handbook

How to insert a special character, for example, "in MySQL"?

I execute an insert request from a shell script that reads data from multiple files. Some of the data that needs to be inserted is contained ' in the text, and MySQL continues to give errors

 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near Development & Empowerment, Youth Affairs ',' Himachal Pradesh ' at line 1

This is the actual text: Women Development & Empowerment, Youth Affairs .

+7
source share
5 answers

You need to avoid a quote, for example:

 'Women\ Development & Empowerment, Youth Affairs'

Note that if you create an SQL statement in a language similar to PHP, there are functions available for this.

In PHP, for example, there is mysql_real_escape_string that will take care of this for you. Please note that prepared statements should be preferred because this is difficult to do wrong.

See also:

+14
source

You will need to avoid entering strings before passing them to MySql.

Escape character list:

 Character Escape 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.
+3
source

You need to escape the backslash character \

Women \ Development and Empowerment, Youth Affairs

+1
source

You need to avoid quotation. You can do this by doubling it in your insert request; i.e. use '', not '. These are two single quotation marks, not one double quotation mark.

+1
source

What use are you using on the server side?

There are many ways in which you can escape the string, I will prefer this!

For example: you can use mysql_escape_string data you enter in the query so that it automatically exits elements such as " ' "

mysql_escape_string is a php function provided that you use php

0
source

More articles:


All Articles