Avoiding single quotes in MySQL

Question

How to insert a value in MySQL consisting of single or double quotes. i.e

This is Ashok Pen. 

A single quote will create problems. There may be other escape characters.

How do you insert data correctly?

Answer 1

Simply put:

SELECT 'This is Ashok' Pen.';

So, inside the line, replace each individual quote with two of them.

Or:

SELECT 'This is Ashok\ Pen.'

Escape it =)

Answer 2

See my answer on “How to escape characters in MySQL”

An escaping function will be built into any library that you use to communicate with MySQL, for example, in PHP you can use mysqli_real_escape_string or PDO :: quote

Answer 3

'this is an escape character. So your line should be:

This is Ashok Pen

If you use any interface code, you need to replace the string before sending data to the stored procedure.

For example, in C # you can do

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

and then pass the value to the stored procedure.

Answer 4

If you use prepared instructions, 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(); 

Answer 5

Use this code:

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

This will solve your problem because the database cannot detect special string characters.

Answer 6

There is another way to do this, which may or may not be safer, depending on your point of view. This requires MySQL 5.6 or later due to the use of a specific string function: FROM_BASE64 .

Suppose you have this message that you want to insert:

“Ah,” the almost headless Nick waved an elegant hand, “the question is unimportant ... Not that I really wanted to join ... I think I would have applied, but obviously I'm not fulfilling the requirements.”

This quote has a bunch of single- and double quotes, and it would be very difficult to insert it into MySQL. If you paste this from a program, quotes, etc. are easily avoided. But, if you need to paste this into an SQL script, you will have to edit the text (to avoid quotes), which can lead to errors. or sensitive to word wrap, etc.

Instead, you can encode Base64 text so that you have a “clean” line:

 JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K 

Some notes on Base64 encoding:

1. Base64 encoding is a binary encoding, so you better make sure that you correctly specify your character set when encoding, because MySQL is going to decode the encoded Base64 string into bytes, and then interpret them. Make sure base64 and MySQL agree on character encoding (I recommend UTF-8).
2. I wrapped a row in 50 columns for readability when stack overflows. You can wrap it in any number of columns that you want (or not wrap at all), and it will still work.

Now, to load this into MySQL:

 SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K ')); 

This will insert without any complaints, and you did not need to manually escape the text inside the line.

Answer 7

You should avoid special characters with the \ character.

 This is Ashok Pen. 

becomes:

 This is Ashok\ Pen. 

Answer 8

In PHP use mysqli_real_escape_string ..

An 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 = " 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); ?> 

Answer 9

You can use this code,

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

if mysqli_real_escape_string () is not working.

Answer 10

If you want to save (') the apostrophe in the database, use this code below

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

$ new_value can be stored in the database.

Answer 11

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

PHP Manual Adds Slashes

Answer 12

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

Answer 13

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

In Perl DBI, for example, you can use:

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

Answer 14

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

Answer 15

How I do using Delphi:

TheString for "escape":

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

Decision:

 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 "\", which allows you to easily insert or update text fields in MySQL.

Similar functions are in all programming languages!

Answer 16

Use addlahes () or mysql_real_escape_string ().