Mysql is not saving answer with ' character in it

Question

Possible Duplicate:
How do I handle single quotes inside a SQL query in PHP?

Greeting , I have a small script which is used for applications and it saves questions answer into the database. The script is given below:

while(list($QKey,$QVal) = each($AppQuestions)) {
    $result2= mysql_query("
         INSERT INTO forum_app_answers (AID, AppID, Question, Answer)".
         " VALUES (NULL, '$AppID', '$Questions[$QKey]', '$QVal')"
     ) or die(mysql_error());

Now the problem is that if someone write ' character in the answer , the data doesnt get saved. For simple writing its okay . The problem is only if the answer contains ' in it. any help will be highly appreciated tx

The following error occures: 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 's GF. Channel Services')' At Line 1

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ) — thaJeztah, Feb 02 '13 at 21:52
Have a look at mysql_real_escape_string http://php.net/manual/de/function.mysql-real-escape-string.php — axel.michel, Feb 02 '13 at 21:54
Read my comment above: You clearly insert variables into your queries without escaping them. Your application is therefore vulnerable to SQL injections, which may expose unwanted information and may even allow people to delete all of your data! **Read this site to see what can go wrong:** http://www.unixwiz.net/techtips/sql-injection.html — thaJeztah, Feb 02 '13 at 21:54

score 2 · Answer 1 · answered Feb 02 '13 at 21:52

2

Use prepared statements. Look up PDO and use prepared statements.

mysql_ is deprecated.

After connecting with $dbh = new PDO(),

    $sql = 'sql';
    $stmt = $dbh->prepare($sql);
    $stmt->execute($params);

answered Feb 02 '13 at 21:52

Zevi Sternlicht

5,399
19
31

ajon · Answer 2 · 2013-02-03T04:34:09.283

1

do the following:

$QVal = $mysqli->real_escape_string($QVal);
$query = "INSERT INTO forum_app_answers (AID, AppID, Question, Answer) 
          VALUES (NULL, '$AppID', '$Questions[$QKey]', '$QVal')";
//  $mysqli is previously defined
$mysqli->query($query);
if ($mysqli->errno !=0){
    printf("you have an error in your query %s", $mysqli->error);
}

edited Feb 03 '13 at 04:34

answered Feb 02 '13 at 21:56

ajon

7,868
11
48
86

This will escape all the intended single quotes in the query as well, so it won't work. Rather, need to escape just the `$QVal` string before putting it into the query. – lxop Feb 02 '13 at 22:03

score 0 · Accepted Answer · answered Feb 02 '13 at 21:55

0

You may try:

while(list($QKey,$QVal) = each($AppQuestions)) {
    $result2= mysql_query("
       INSERT INTO forum_app_answers
           (AID, AppID, Question, Answer)". " 
       VALUES (
           NULL,
           '$AppID',
           '$Questions[$QKey]',
            '". mysql_real_escape_string($QVal). "')
      ") or die(mysql_error());

Without mysql_real_escape_string() your script also has huge security issues.

answered Feb 02 '13 at 21:55

Narcis Radu

2,519
22
33

I tried Nacris way and it worked :) – Salman Khaliq Feb 02 '13 at 22:36

Mysql is not saving answer with ' character in it

3 Answers3