|
Home
/ Technical Support / MySQL
Key Offerings:
B2B and B2C E-Business
Solutions
Offshore
Software Development Outsourcing
Strategic Consulting
Offshore Software Outsourcing
ALTOROS Systems
is headquartered in Tampa, Florida and maintains an office
near Boston, Massachusetts and technology development center
in Belarus and Russia. ALTOROS specializes on providing value-added
e-commerce and web-based software development and offshore
software outsourcing services to emerging enterprises helping
them successfully plan and implement business initiatives.
Contact Us for more
information.
Strings
A string is a sequence of characters, surrounded by either single quote (') or double quote (") characters (only the single quote if you run in ANSI mode). Examples:
'a string'
"another string"
Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (\), known as the escape character. MySQL recognises the following escape sequences:
\0
An ASCII 0 (NUL) character.
\'
A single quote (') character.
\"
A double quote (") character.
\b
A backspace character.
\n
A newline character.
\r
A carriage return character.
\t
A tab character.
\z
ASCII(26) (Control-Z). This character can be encoded to allow you to work around the problem that ASCII(26) stands for END-OF-FILE on Windows. (ASCII(26) will cause problems if you try to use mysql database < filename.)
\\
A backslash (\) character.
\%
A % character. This is used to search for literal instances of % in contexts where % would otherwise be interpreted as a wildcard character. See String comparison functions.
\_
A _ character. This is used to search for literal instances of _ in contexts where _ would otherwise be interpreted as a wildcard character. See String comparison functions.
Note that if you use \% or \_ in some string contexts, these will return the strings \% and \_ and not % and _.
There are several ways to include quotes within a string:
· A ' inside a string quoted with ' may be written as ''.
· A " inside a string quoted with " may be written as "".
· You can precede the quote character with an escape character (\).
· A ' inside a string quoted with " needs no special treatment and need not be doubled or escaped. In the same way, " inside a string quoted with ' needs no special treatment.
The SELECT statements shown here demonstrate how quoting and escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+
If you want to insert binary data into a string column (such as a BLOB), the following characters must be represented by escape sequences:
NUL
ASCII 0. You should represent this by \0 (a backslash and an ASCII 0 character).
\
ASCII 92, backslash. Represent this by \\.
'
ASCII 39, single quote. Represent this by \'.
"
ASCII 34, double quote. Represent this by \".
If you write C code, you can use the C API function mysql_real_escape_string() to escape characters for the INSERT statement. See C API function overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See Perl\line DBI Class.
You should use an escape function on any string that might contain any of the special characters listed above!
Alternatively, many MySQL APIs provide some sort of placeholder capability that allows you to insert special markers into a query string, and then bind data values to them when you issue the query. In this case, the API takes case of escaping special characters in the values for you automatically.
|
