Apostrophe does not work in mysql/PHP

Question

Possible Duplicate:
apostrophes are breaking my mysql query in PHP

My Android App is sending a message to my server (PHP script) The PHP script is writing the message into MySQL.

It works fine, but all messages containing an apostrophe ' (e.g. he's going to school) are not written into the database.

Here the php script:

function deq($s)
{
    if($s == null)
        return null;
    return
        get_magic_quotes_gpc() ?
        stripslashes($s) : $s;
}

  if(md5(deq($_REQUEST['blub']).deq($_REQUEST['blub'])."blabla") == $_REQUEST['hash']){
    mysql_connect("localhost", "blub", "blub") or die(mysql_error());
    mysql_select_db("blub") or die(mysql_error());

   // mysql_set_charset('UTF8');  // does not work as too old php
   mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET COLLATION_CONNECTION = 'utf8_unicode_ci'");

    $mysqldate = gmdate( 'Y-m-d H:i:s');

    $language = (int) $_REQUEST['language'];

    $device = $_REQUEST['device'];

    $ipaddress = $_SERVER['REMOTE_ADDR'];


    mysql_query("INSERT INTO test(username, text, language, rating, checked, date)
    VALUES('".$_REQUEST['username']."', '".$_REQUEST['text']."', '".$language."', '0' , 'false', '".$mysqldate."') ")
    or die(mysql_error());

    mysql_close();

Read: [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) — deceze, Nov 04 '12 at 14:03
You are using [an obsolete database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also exposing yourself to [SQL injection attacks](http://bobby-tables.com/) that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Nov 04 '12 at 14:03

score 2 · Accepted Answer · answered Nov 04 '12 at 13:59

2

All you need to escape ' like below

$_REQUEST['text'] = mysql_real_escape_string($_REQUEST['text']);

answered Nov 04 '12 at 13:59

GBD

15,847
2
46
50

score 1 · Answer 2 · answered Nov 04 '12 at 14:02

1

Don't use magic quotes. use mysqli_real_escape_string or mysql_real_escape_string if you don't want to use mysqli.

answered Nov 04 '12 at 14:02

Nothing4You

146
2
10

score 1 · Answer 3 · answered Nov 04 '12 at 14:03

1

You have discovered SQL injections: The apostrophe in the input "gets out" of the apostrophe you put in the query. This causes some of the input to be considered part of the query. You can use mysql_real_escape_string to secure the input before you use it in a query. But in general, there are better solutions for this (e.g. prepared statements).

^{You are, by the way, using the outdated mysql_ functions in PHP (see the big red warning on http://php.net/mysql_query)}

answered Nov 04 '12 at 14:03

vicvicvic

6,025
4
38
55

Thx for the help! What should I use instead of mysql_? Is this a problem? security risk? – tobias Nov 04 '12 at 14:12
1

See this http://php.net/manual/en/mysqlinfo.api.choosing.php -- PDO is probably the most modern API (allows you to work with other systems than MySQL in a unified fashion). Both mysqli and PDO allow you to use prepared statements which make it easier to avoid security risks and also can improve performance. I don't think `mysql` in itself is a security risk, but it's too easy to program insecurely with it. – vicvicvic Nov 04 '12 at 14:19

score 1 · Answer 4 · answered Nov 04 '12 at 14:20

Looking at the description above you need to escape input data with slashes using mysql_real_escape_string function

Apostrophe in input data when utilized in sql query will break the sql query resulting into sql injection

So always sanitize input data with mysql_real_escape_string() function before utilizing into sql query

For more documentation about mysql_real_escape_string() function please refer the documentation mentioned in below url

http://php.net/manual/en/function.mysql-real-escape-string.php

Apostrophe does not work in mysql/PHP

4 Answers4