Storing data in MySQL

Question

I want to store some values like "st paul's school" in mysql database. However due to the special symbol ' I am unable to store such values in mysql. I tried varchar datatype but it doesn't work. Please suggest a suitable method or datatype to store ' in mysql.

This is pretty straightforward to search for: http://stackoverflow.com/questions/887036/insert-value-in-mysql-containg-single-quotes — Tim B, Feb 05 '13 at 12:44

score 4 · Answer 1 · answered Feb 05 '13 at 12:44

4

Add two single quotes like this '' or escape it like this \'

answered Feb 05 '13 at 12:44

Waqar

758
4
15

But Iam fetching these names from a website. so I never know the position of single quote in the names. so i cant add double quote at the position of single quote. – shilpa Feb 05 '13 at 12:49
1

How are you inserting these names into the database ? If you are updating via a application (C#, Java e.t.c) Can you not use a string replace function to replace instances of single quotes with two single quotes ? – Waqar Feb 05 '13 at 12:55
The name can also be st mary's school in place of st paul's school. for using replace function, I need to check every character of every name. indirectly it will affect performance of my code. Can you suggest me a suitable datatype for storing values with apostrof in mysql. – shilpa Feb 05 '13 at 13:04

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

If you are doing it directly on MySQL Server, double the single quote and it will work. Example

INSERT INTO tableName VALUES ('St. Paul''s School')

But on front-end, use PreparedStatements for this.

If you are using PHP, use PDO or MySQLI extension on this to avoid SQL Injection. Please see thearticle below,

How to prevent SQL injection in PHP?

edited May 23 '17 at 11:45

Community

1
1

answered Feb 05 '13 at 12:43

John Woo

258,903
69
498
492

But Iam fetching these names from a website. so I never know the position of single quote in the names. so i cant add double quote at the position of single quote. – shilpa Feb 05 '13 at 12:48
what programming are you using? `PHP`? – John Woo Feb 05 '13 at 12:49

score 0 · Answer 3 · answered Feb 05 '13 at 12:46

0

If u have variable with this apostrof use htmlentities

 $your_var = "St. Paul's School";
 $your_var = htmlentities($your_var);
 INSERT INTO tableName VALUES ('$your_var')

If u have just string u can do double quotes like that

 INSERT INTO tableName VALUES ("St. Paul's School")

answered Feb 05 '13 at 12:46

echo_Me

37,078
5
58
78

if u fetch it from other variables , here the first solution for u – echo_Me Feb 05 '13 at 12:51

score 0 · Answer 4 · answered Feb 05 '13 at 12:52

Just replace the quote sign with it's entity. If you are using php

<?php
    $str = "st paul's school";
    $str = str_replace("\'","&acute;",$str);
    $str = str_replace("\"","&quot;",$str);
?>

You can also use

$str= htmlentities($str,ENT_QUOTES, 'UTF-8');

score 0 · Answer 5 · answered Feb 05 '13 at 12:54

0

You can use mysqli_real_escape_string($text) to escape those ' in the text you are inserting to the database.

answered Feb 05 '13 at 12:54

zurfyx

31,043
20
111
145

Storing data in MySQL

5 Answers5