Error in SQL Syntax When Using ' '

Question

Possible Duplicate:
Why string with single quotes raises error when inserted in DB?
MySQL error when inserting data containing apostrophes (single quotes)?

Whenever I put single quotes (' ') in a textarea, I always get the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test'','1351029587','10/23/2012','0','0','1492815560','0')' at line 1

I've inserted an html remove function:

$title = html_remove($title);
$body = html_remove($body);

function html_remove($input) {
    $old = array('<','>');
    $new = array('&lt;','&gt;');
    $input = str_replace($old, $new, $input);
    return $input;
    }

An yes, $title and $body are valid and working variables.

$body = $_POST['body'];
$title = $_POST['title'];

Not sure if that's exactly how you do it, but that's pretty much all I got.

That's a SQL injection: http://php.net/manual/en/security.database.sql-injection.php Fix that, and you can get rid of the HTML remover. — Pekka, Oct 23 '12 at 22:06
Why are you doing that str_replace stuff when PHP has htmlspecialchars() and htmlentities() to do that kind of stuff for you? — GordonM, Oct 23 '12 at 22:07
I strongly recommend making sure your SQL is constructed using parameterised queries. See http://www.phptherightway.com/#databases — Spudley, Oct 23 '12 at 22:11
You don't want to be exploited by [moms](http://xkcd.com/327/). — Benjamin Crouzier, Oct 23 '12 at 22:15
possible duplicate of [Why string with single quotes raises error when inserted in DB?](http://stackoverflow.com/questions/5125785/why-string-with-single-quotes-raises-error-when-inserted-in-db) or [MySQL error when inserting data containing apostrophes (single quotes)?](http://stackoverflow.com/questions/7600661/mysql-error-when-inserting-data-containing-apostrophes-single-quotes) and [**many more**](http://www.google.com/search?q=site:stackoverflow.com+php%20sql%20error%20with%20quotes) — mario, Oct 23 '12 at 22:35

score 0 · Answer 1 · edited May 23 '17 at 11:55

You should immediately stop using your code. It is vulnerable to SQL injection. In addition, mysql_ functions are being deprecated. You should use prepared statements with bound variables using mysqli_ or PDO functions.

You may want to look into the strip_tags function that will strip HTML and PHP tags from a string or htmlentities to convert all applicable characters to HTML entities. Read here for the difference and examples.

Here's an example to get you on the right path:

<?php

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');

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

$stmt = mysqli_prepare($link, "INSERT INTO myTable (body, title) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, 'ss', $_POST[body], $_POST[title]);

/* execute prepared statement */
mysqli_stmt_execute($stmt);

printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));

/* close statement and connection */
mysqli_stmt_close($stmt);

/* close connection */
mysqli_close($link);
?>

jlibert · Answer 2 · 2012-10-23T22:21:57.177

0

use the following variables in a prepared statement:

$body=htmlentities($_POST['body']);
$title=htmlentities($_POST['title']);

htmlentities() will convert the < and > to < and > and the executed prepared statement will escape the ' and " characters with a \

edited Oct 23 '12 at 22:21

answered Oct 23 '12 at 22:14

jlibert

155
5

Error in SQL Syntax When Using ' '

2 Answers2