CODEWITHSUNDEEP
phpmysqlutf-8
Popa Gabriel
Popa Gabriel

Reputation: 31

PHP and MySQL special characters error

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.

Upvotes: 0

Views: 3577

Answers (2)

Sepultura
Sepultura

Reputation: 1047

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.

Upvotes: 2

Psi
Psi

Reputation: 6793

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

Upvotes: 1

Related Questions