PHP and MySQL special characters error

Question

I am trying to make an article poster that works perfectly until I put special characters in the html form (like ;,! etc.). I Googled it and found something about the table collation (which is utf8_unicode_ci by default).

I have <meta charset="utf-8"> into the header file and mysqli_set_charset($conn, 'utf8') after connection to the database. Also the form has accept-charset="utf-8" attribute.

Here is what happens after sending the form:

if(isset($_POST['sendForm']))
{
    $articleTitle = $_POST['title'];
    $articleText = $_POST['text'];
    $name = $_SESSION['name'];

    $currentDateMySQL = date("Y.m.d");

    $sql = "INSERT INTO articles (title, text, owner, date_added) VALUES ('$articleTitle', '$articleText', '$name', '$currentDateMySQL')";
$result = mysqli_query($conn, $sql);

    if($result === false)
    {
        $color = "red";
        $infoText = "Could not insert your information into the database. Error number: <b>" . mysqli_errno($conn) . "</b>. :( Try again.";
    }
    else
    {
        $color = "green";
        $infoText = "Succesfully writen the article into the database. :)";
    }
}

Also the given error number is 1064. There is no error in the SQL code, it works perfectly without special characters.

Sepultura · Accepted Answer · 2017-03-04 12:24:43Z

2

You need do escape every input you trying to insert into a database otherwise you risking sql-injection attacks:

$articleText = mysql_real_escape_string($articleText);

Also you shouldn't use native sql directly anymore, it is deprecated. You should use prepared statements instead.

edited Mar 4, 2017 at 12:24

answered Mar 4, 2017 at 12:18

Sepultura

1,0571 gold badge11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sepultura Over a year ago

it escapes special characters

Popa Gabriel Over a year ago

I use mysqli not mysql but I do not use PDO

Popa Gabriel Over a year ago

And thank you! That's how I did at registration. I forgot. Thank you again!

Psi · Accepted Answer · 2017-03-04 12:19:56Z

1

If you changed your table collation after creation, it does not mean your column collation does match.

All of the following charsets should match so that your data is inserted correctly:

column charset collation
connection charset

Even better, to have the same charset everywhere:

defaut charset
database charset
table charset
column charset
connection charset

answered Mar 4, 2017 at 12:19

Psi

6,8283 gold badges19 silver badges30 bronze badges

1 Comment

Popa Gabriel Over a year ago

Thank you but the answer was to use mysqli_real_escape_string. :)

Collectives™ on Stack Overflow

PHP and MySQL special characters error

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related