MySQL Error When Inserting Data Containing Apostrophes (Single Quotes)

MySQL error when inserting data containing apostrophes (single quotes)?

Escape the quote with a backslash. Like 'Kellogg\'s'.

---

Here is your function, using mysql_real_escape_string:

function insert($database, $table, $data_array) { 
    // Connect to MySQL server and select database 
    $mysql_connect = connect_to_database(); 
    mysql_select_db ($database, $mysql_connect); 

    // Create column and data values for SQL command 
    foreach ($data_array as $key => $value) { 
        $tmp_col[] = $key; 
        $tmp_dat[] = "'".mysql_real_escape_string($value)."'"; // <-- escape against SQL injections
    } 
    $columns = join(',', $tmp_col); 
    $data = join(',', $tmp_dat);

    // Create and execute SQL command 
    $sql = 'INSERT INTO '.$table.'('.$columns.')VALUES('. $data.')'; 
    $result = mysql_query($sql, $mysql_connect); 

    // Report SQL error, if one occured, otherwise return result 
    if(!$result) { 
        echo 'MySQL Update Error: '.mysql_error($mysql_connect); 
        $result = ''; 
    } else { 
        return $result; 
    } 
}

How to insert a value that contains an apostrophe (single quote)?

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Escaping single quote in PHP when inserting into MySQL

You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

http://us3.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

Insert a value containing single quotes in MySQL

You need to use \ (Escape) character to insert single quotes and double quotes.

INSERT INTO table_name(`clomn1`) VALUES ('Ali said, "This is Ashok\'s Pen."')

Why string with single quotes raises error when inserted in DB?

Single quotes are not forbidden in any way. I'll simply assume that you got an error inserting it into the database. This is likely due to the omission of mysql_real_escape_string() on input values.

You will get an SQL error if you try INSERT ... ('O'Reilly') which is the whole point of the SQL escaping functions.

(This is why magic_quotes were originally introduced: to make SQL work out of the box for newcomers. - Not to make that particularly secure.)

How to escape single quotes in MySQL

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 =)

Error inserting data into varchar that contains apostrophe?

Use the quote method on the connection object:

quote(value, column = nil)

API Documentation Link

Quotes the column value to help prevent SQL injection attacks.
Example:

my_name    = ActiveRecord::Base.connection.quote("John O'Neil")
my_address = ActiveRecord::Base.connection.quote("R'lyeh")

query = "INSERT INTO companies (name,address) VALUES (#{my_name}, #{my_address})"

ActiveRecord::Base.connection.execute(query);

Original Post:

See this post: Escaping a single quotation within SQL query

MySQL statement python escape single quote '

Return a tuple of string template and tuple of variables and the cursor can execute (template, (v1,v2,..))

cursor.execute(‘insert into tablename (c, d) values (%s, %s)’, (v1, v2))

Based on the API Docs

Edit 2: A more complete example

def query(self, item):
  values = ', '.join(['%s']*len(item.keys()))
  stmt = "INSERT INTO income_statement({columns}) VALUES ({values})".format(
      columns=', '.join(item.keys()),
      values=values
  )
  # self.item_to_text(item) must be a tuple
  return (stmt, self.item_to_text(item))

# use it like so
cursor.execute(query(item))

Edit 3:
I am pretty certain that if you really want to pass the statement as a single string you’d have to have a \ present in the string itself thus using
INT\\’L

Edit 4:

def item_to_text(self, item):
    return ', '.join(item.values()) # assuming item.values() returns a list or a tuple

---

Related Topics