How can i store a string with quotes in it into a sql db?

Question

I have a site and the user inputs a title. I want the title to be able to use any punctuation. My problem is ill have a query:

"INSERT INTO table(title, body) VALUES ('$title','$body')";

where $title and $body are GET vars. What happens it when i put a quote in for the title it acts as if it ends the string and creates and invalid sql query. Say i have

$title = "I'm entering a title";
"INSERT INTO table(title, body) VALUES ('$title','$body')";
//"INSERT INTO table(title, body) VALUES ('I'm entering a title','$body')";

It ends the string. I've tried using all double quotes and escape characters but nothing. Does anyone know a solution?

Maybe you could use html entities – bozdoz Nov 26 '12 at 05:51 — bozdoz, Nov 26 '12 at 05:51
Obligatory link: http://bobby-tables.com/ – Amadan Nov 26 '12 at 05:52 — Amadan, Nov 26 '12 at 05:52

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

Your query is vulnerable with SQL Injection but using PHP's PDO or MySQLi helps you solve that problem (also allows you to insert single quotes in the database), Please read the article below

How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:24

Community

1
1

answered Nov 26 '12 at 05:52

John Woo

258,903
69
498
492

So mysqli and bindparams completely fool proofs sql injections? – user1846761 Nov 26 '12 at 06:03
for me, yes. [SQL injection that gets around **mysql_real_escape_string**](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – John Woo Nov 26 '12 at 06:05

score 1 · Answer 2 · answered Nov 26 '12 at 05:55

Use these two functions while entring data to database and out from database.

/****************************************/
/* Encode special chars                 */
/*                                      */
/****************************************/

function DBin($string) 
{
    return  trim(htmlspecialchars($string,ENT_QUOTES));
}

/****************************************/
/* Decode special chars                 */
/*                                      */
/****************************************/

function DBout($string) 
{
    $string = trim($string);
    return htmlspecialchars_decode($string,ENT_QUOTES);
}

score 0 · Answer 3 · answered Nov 26 '12 at 05:54

0

You should sanitize your variables via PHP first and then send them clean via PDO or MySQLi...

answered Nov 26 '12 at 05:54

Marco Berrocal

362
1
10

score 0 · Answer 4 · answered Nov 26 '12 at 05:58

0

You can try this php function..

mysql_real_escape_string

    $title1       = "I'm entering a title";
    $title        = mysqli_real_escape_string($title1);
    "INSERT INTO table(title, body) VALUES ('$title','$body')";

Try with this, hope this ll help you...

answered Nov 26 '12 at 05:58

Basith

1,077
7
22

[SQL injection that gets around mysql_real_escape_string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – John Woo Nov 26 '12 at 06:05
do i need this if i am using the mysqli prepare route? the php manual says it is discouraged for use and mysqli should be used – user1846761 Nov 26 '12 at 06:06
wrong! mysqli_real_escape_string expects 2 parameters – Danniel Little Apr 16 '18 at 19:39

score 0 · Answer 5 · edited Nov 26 '12 at 06:23

0

You need to mysqli_real_escape_string your SQL.

edited Nov 26 '12 at 06:23

mu is too short

426,620
70
833
800

answered Nov 26 '12 at 06:00

zorro

3
4

score 0 · Answer 6 · answered Nov 26 '12 at 06:04

0

There is already a built-in method for this situation... have a look in mysql_escape_string method... see the below code...

$title = "I'm entering a title";
$title = mysql_escape_string( $title );
// $title === I\'m entering a title
"INSERT INTO table(title, body) VALUES ('$title','$body')";
// "INSERT INTO table(title, body) VALUES ('I\'m entering a title','$body')";

answered Nov 26 '12 at 06:04

Naz

2,520
2
16
23

[SQL injection that gets around mysql_real_escape_string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – John Woo Nov 26 '12 at 06:06

How can i store a string with quotes in it into a sql db?

6 Answers6