How to escape single quotes in MySQL?

Question

How do I insert a value in MySQL that consist of single or double quotes. i.e

This is Ashok's Pen.

The single quote will create problems. There might be other escape characters.

How do you insert the data properly?

Wifeless

This may be dupe hammered into How can I prevent SQL injection in PHP?... – Handclap

Answer 1

Put quite simply:

SELECT 'This is Ashok''s Pen.';

So inside the string, replace each single quote with two of them.

Or:

SELECT 'This is Ashok\'s Pen.'

Escape it =)

Pruritus

You might want to update your post to include mysql_real_escape_string() or addslashes() as possible solutions... as in one of the comments below, the OP states that they're just reading from file and inserting. – Baalbeer

The mysql_* methods are not recommended for future use, as it is deprecated. – Gausman

That is the right answer, although these days the best option is to use one of the – Peraea

Answer 2

' is the escape character. So your string should be:

This is Ashok''s Pen

If you are using some front-end code, you need to do a string replace before sending the data to the stored procedure.

For example, in C# you can do

value = value.Replace("'", "''");

and then pass value to the stored procedure.

Saucer

I m not inserting data manually, I extract it from File, and there will be vaklues: Ashok's pen. How to insert it. creaetd a procedure for doing this.. how to make it correct. – Wifeless

FWIW minor nit: backslash is "the escape character"; '' (two single quotes) is a special alternative allowed for "escaping" a single-quote character. – Emotionalize

Answer 3

See my answer to "How to escape characters in MySQL"

Whatever library you are using to talk to MySQL will have an escaping function built in, e.g. in PHP you could use mysqli_real_escape_string or PDO::quote

Interoffice

I m not inserting data manually, I extract it from File, and there will be values: Ashok's pen. How to insert it. created a procedure for doing this.. how to make it correct. – Wifeless

Just a note, This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.. – Constanta

Answer 4

Use this code:

<?php
    $var = "This is Ashok's Pen.";

    mysql_real_escape_string($var);
?>

This will solve your problem, because the database can't detect the special characters of a string.

Raseda

Just a note, This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.. – Constanta

Answer 5

If you use prepared statements, the driver will handle any escaping. For example (Java):

Connection conn = DriverManager.getConnection(driverUrl);
conn.setAutoCommit(false);
PreparedStatement prepped = conn.prepareStatement("INSERT INTO tbl(fileinfo) VALUES(?)");
String line = null;
while ((line = br.readLine()) != null) {
    prepped.setString(1, line);
    prepped.executeQuery();
}
conn.commit();
conn.close();

Gausman

Answer 6

There is another way to do this which may or may not be safer, depending upon your perspective. It requires MySQL 5.6 or later because of the use of a specific string function: FROM_BASE64.

Let's say you have this message you'd like to insert:

"Ah," Nearly Headless Nick waved an elegant hand, "a matter of no importance. . . . It's not as though I really wanted to join. . . . Thought I'd apply, but apparently I 'don't fulfill requirements' -"

That quote has a bunch of single- and double-quotes and would be a real pain to insert into MySQL. If you are inserting that from a program, it's easy to escape the quotes, etc. But, if you have to put that into a SQL script, you'll have to edit the text (to escape the quotes) which could be error prone or sensitive to word-wrapping, etc.

Instead, you can Base64-encode the text, so you have a "clean" string:

SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K

Some notes about Base64-encoding:

1. Base64-encoding is a binary encoding, so you'd better make sure that you get your character set correct when you do the encoding, because MySQL is going to decode the Base64-encoded string into bytes and then interpret those. Be sure base64 and MySQL agree on what the character encoding is (I recommend UTF-8).
2. I've wrapped the string to 50 columns for readability on Stack Overflow. You can wrap it to any number of columns you want (or not wrap at all) and it will still work.

Now, to load this into MySQL:

INSERT INTO my_table (text) VALUES (FROM_BASE64(' SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K '));

This will insert without any complaints, and you didn't have to manually-escape any text inside the string.

Agathy

Answer 7

You should escape the special characters using the \ character.

This is Ashok's Pen.

Becomes:

This is Ashok\'s Pen.

Orren

Answer 8

If you want to keep (') apostrophe in the database use this below code:

$new_value = str_replace("'","\'", $value); 

$new_value can store in database.

Dori

Answer 9

In PHP, use mysqli_real_escape_string.

Example from the PHP Manual:

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");

$city = "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", mysqli_sqlstate($link));
}

$city = mysqli_real_escape_string($link, $city);

/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

mysqli_close($link);
?>

Festoon

Answer 10

You can use this code,

<?php
     $var = "This is Ashok's Pen.";
     addslashes($var);
?>

if mysqli_real_escape_string() does not work.

Francefrancene

Answer 11

If you are using PHP, just use the addslashes() function.

PHP Manual addslashes

Murguia

Answer 12

$var = mysqli_real_escape_string($conn, $_POST['varfield']);

Amesace

Answer 13

For programmatic access, you can use placeholders to automatically escape unsafe characters for you.

In Perl DBI, for example, you can use:

my $string = "This is Ashok's pen";
$dbh->do("insert into my_table(my_string) values(?)",undef,($string)); 

Kermit

Answer 14

Maybe you could take a look at function QUOTE in the MySQL manual.

Kristofor

Answer 15

The way I do, by using Delphi:

TheString to "escape":

TheString=" bla bla bla 'em some more apo:S 'em and so on ";

Solution:

StringReplace(TheString, #39,'\'+#39, [rfReplaceAll, rfIgnoreCase]);

Result:

TheString=" bla bla bla \'em some more apo:S \'em and so on ";

This function will replace all Char(39) with "\'" allowing you to insert or update text fields in MySQL without any problem.

Similar functions are found in all programming languages!

Arrowood

Answer 16

Use either addslahes() or mysql_real_escape_string().

Impetuosity

Just a note, This, mysql_real_escape_string() extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.. – Constanta

Yup, need to add an i now mysqli_real_escape_string php.net/manual/en/mysqli.real-escape-string.php – Impetuosity

It would be better to update your answer (e.g. comments may disappear at any time). – Handclap

Answer 17

This is how my data as API response looks like, which I want to store in the MYSQL database. It contains Quotes, HTML Code , etc.

Example:-

{

rewardName: "Cabela's eGiftCard $25.00",

shortDescription: '<p>adidas gift cards can be redeemed in over 150 adidas Sport Performance, adidas Originals, or adidas Outlet stores in the US, as well as online at&nbsp;<a href="http://adidas.com/">adidas.com</a>.</p>

terms: '<p>adidas Gift Cards may be redeemed for merchandise on&nbsp;<a href="http://adidas.com/">adidas.com</a>&nbsp;and in adidas Sport Performance, adidas Originals, and adidas Outlet stores in the United States.'

}

SOLUTION

CREATE TABLE `brand` (
`reward_name` varchar(2048),
`short_description` varchar(2048),
`terms` varchar(2048),  
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

While inserting , In followed JSON.stringify()

    let brandDetails= {
    rewardName: JSON.stringify(obj.rewardName),  
    shortDescription: JSON.stringify(obj.shortDescription),
    term: JSON.stringify(obj.term),
     }

Above is the JSON object and below is the SQL Query that insert data into MySQL.

let query = `INSERT INTO brand (reward_name, short_description, terms) 
VALUES (${brandDetails.rewardName}, 
(${brandDetails.shortDescription}, ${brandDetails.terms})`;

Its worked....

If nothing works try this :

var res = str.replace(/'/g, "\\'");
var res = res.replace(/"/g, "\\\"");

It adds the \ escape character to all(every) occurrences of ' and "

Not sure if its the correct/professional way to fix the issue

I'm guessing it will work but in actual content, every single and double quotes will be replaced with \ character

Heulandite

Answer 18

As a Python user I replace the quote with a raw "'":

don_not_work_str = "This is Ashok's Pen."
work_str = don_not_work_str.replace("'", r"\'")

Virgievirgil

Answer 19

For Python，I tried many ways to escape '"', not work, cuz I have various input.

Finally, I just use these code, the data in database was same like the input.

tmp = val.replace('\\', r'\\')
ret = tmp.replace('"', r'\"')

Barret

Answer 20

If you want to output the result with single and double quotes below in MySQL:

I'm saying "OK".

Then, you can write it as shown below:

SELECT 'I\'m saying "OK".';

SELECT 'I''m saying "OK".';

SELECT "I'm saying \"OK\".";

SELECT "I'm saying ""OK"".";

*The doc explains how to escape single and double quotes.

Deonnadeonne