HTML form with spanish characters displayed wrong in MySQL database

Question

So I'm sending data to my MySQL database through a HTML form.

When I send a word with special characters like "ñ" or accents "á, é, í.." and when I check the result in the tables those characters are displayed as "ã±".

I tried pretty much everything:

my html form page header has

<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">

The form accepts UTF-8 with <form accept-charset="UTF-8">

I also added to my action php script:

mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET'utf8'");

Then I tried htmlentities

but all the "ñ" were displayed as ã&

My database, tables, and fields are set in utf8_general_ci, also tried utf8_spanish_ci but nothing changed.

I don't know what else to do, is there anything else I am missing here?

This is the PHP script I'am using:

<?php
// Make a MySQL Connection
$con = mysql_connect("I DELETED THIS");
if (!$con)
{
die('No se pudo conectar a la BD: ' . mysql_error() );
}

mysql_select_db("ldma", $con);

$nombre  = ''; 
$ape1   = ''; 
$ape2   = ''; 
$apodo = ''; 
$errmsg = '';

if ($_POST['enviado']==1)
{
   $nombre   = $_POST['nombre'];
   $ape1   = $_POST['ape1'];
   $ape2 = $_POST['ape2'];
   $apodo = $_POST['apodo'];

   $permitidos = "abcdefghijklmnopqrstuvwxyzÁÉÍÓÚÜüáéíóúñÑABCDEFGHIJKLMNOPQRSTUVWXYZ"; 
   for ($i=0; $i<strlen($nombre); $i++)
   { 
      if (strpos($permitidos, substr($nombre,$i,1))===false)
      {$errmsg = 1; }
         else
         {$errmsg = '';} 


   } 

    /*if (ereg('^[ a-zA-Zñ]+$', $nombre)) 
        {$errmsg = '';
        } 
        else 
        {$errmsg = 1;
        }*/


    if(strlen($nombre) == 0 || strlen($nombre) <= 3) 
    {$errmsg = 1;} 
        else if (strlen($nombre) > 20) 
            {$errmsg = 1;} 

    if(strlen($ape1) == 0 || strlen($ape1) <= 3) 
    {$errmsg = 1;} 
        else if (strlen($ape1) > 15) 
            {$errmsg = 1;} 

    if(strlen($ape2) == 0 || strlen($ape2) <= 3) 
    {$errmsg = 1;} 
        else if (strlen($ape2) > 15) 
            {$errmsg = 1;} 

    if(strlen($apodo) > 15)
    {$errmsg = 1;} 



    if($errmsg == '')
    {

    // Insert a row of information into the table "example"

    $arr = array("nombre", "ape1", "ape2", "apodo"); 
    foreach ($arr as $field)
    {
        $_POST["$field"] = utf8_encode($_POST["$field"]);
        $_POST["$field"] = strtolower($_POST["$field"]);
        $_POST["$field"] = ucwords($_POST["$field"]);
    }

    $sql= "INSERT INTO **** (nombre, ape1, ape2, apodo) 
            VALUES ('$_POST[nombre]', '$_POST[ape1]', '$_POST[ape2]', '$_POST[apodo]')" ;

    if (!mysql_query($sql, $con))
        {
        echo "<center><br /><h2>Al parecer este maestro ya existe, intenta de nuevo.</h2></center> ";
        }
        else {
            echo "<center><br /><h2>Gracias, el maestro ha sido agregado. </h2></center> ";
            }
    }

}

if($errmsg == 1)
    { echo "<center><br/><br/><h2><u>Error: Revisa los datos e intenta de nuevo.</u></h2> </center>
            <center><h2>Recuerda que es recomendable </h2> </center> 
            <center><h2>activar JavaScript en las opciones de tu navegador. </h2></center>";
    }

/*if ($_POST['enviado'] != 1)
    {
    echo 'Error: No se agregaron los datos';
    }*/


mysql_close($con);

?>

Where exactly do you check the result in the tables using what code? — Pekka, Oct 17 '10 at 09:39
@Danny what happens if you output that data again in your HTML page? `ã±` is almost certainly an ISO-8859-1 representation of a UTF-8 character but it's unclear where it happens — Pekka, Oct 17 '10 at 09:50
@Pekka I don't have a page to show the data yet, but it's strange to me too see the "ã±" in my phpmyadmin. Also, I imported more data from a text file that included the "ñ" character and was displayed correcly, this problem only happens when adding data from the html form. — Danny, Oct 17 '10 at 09:53
@Danny the question is whether they are stored that way in the table, or displayed that way by the CPanel. That is what you would have to find out (Re your update: Ah. That points to the form being broken. In that case, you should show the PHP code you are using to insert the data) — Pekka, Oct 17 '10 at 09:54
@動靜能量 yes, it is. Otherwise there will be question marks. there is something with SET NAMES query, it doesn't work somehow. — Your Common Sense, Oct 17 '10 at 10:02
Also check you php script encoding, it might play a role... When you echo an utf8-encoded string as a variable in a latin1-encoded php file, what happens? I am not sure... — greg0ire, Oct 17 '10 at 10:04
@動靜能量, Yeah I'm really sure all is set to UTF-8. I converted the string with utf8_encode but that just added more weird characters in the table. — Danny, Oct 17 '10 at 10:08
@動靜能量: If I were you, I wouldn't appreciate Danny's xenophobia in the for loop of his script ;) @Danny: seriously, if you're using utf8, why filter out people with non-spanish characters in their names? BTW, when you say "all is set to UTF-8", did you also check your php script file encoding? — greg0ire, Oct 17 '10 at 10:22
Ahahahaha! Good points re xenophobia @greg0ire. :) What's more, the `ü` will let in some Germans and Turks, but not all (`ÄÖ` is missing)... But the script file's encoding must be okay, otherwise the characters wouldn't pass the check in the first place — Pekka, Oct 17 '10 at 10:28
@greg0ire script encoding affects nothing. Stop that nonsense please. Only database and html encoding matters. — Your Common Sense, Oct 17 '10 at 10:30
@greg0ire, lol, It's not xenophobia, I just don't want 'mean' people to add other characters. @Col. Shrapnel, the set names were deleted from my code because didn't help. — Danny, Oct 17 '10 at 10:30
@Pekka: fair point! The mistake must be somewhere else then I guess... @Danny, I know, I was just kidding, but I doing this filtering is useless. Escaping your strings is all you need and this is what Pekka tells you about in his answer. @Col. Shrapnel: I know it sounds stupid, but I recall to have had some problems with file encoding influencing variables. I did not understand at the moment, but next time I'll try to find out. — greg0ire, Oct 17 '10 at 10:30

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Your error is the usage of non-multibyte-safe string functions:

 $_POST["$field"] = strtolower($_POST["$field"]);
 $_POST["$field"] = ucwords($_POST["$field"]);

Use multi-byte string functions instead.

Apart from that, your form is vulnerable to SQL injection, something that you need to urgently fix.

edited May 23 '17 at 10:33

Community

1
1

answered Oct 17 '10 at 10:15

Pekka

442,112
142
972
1,088

The problem happens with or without that line. (that utf8_encode was added few seconds ago to see if that helps). – Danny Oct 17 '10 at 10:17
@Danny okay. where do you do the SET NAMES in your script? – Pekka Oct 17 '10 at 10:19
@Pekka, I deleted the lines with the SET NAMES because didn't help, I'am trying again just to verify it really doesn't help. – Danny Oct 17 '10 at 10:22
@Danny the set names should do the job. – Pekka Oct 17 '10 at 10:22
Ok so I added the set names again, but it deletes all the characters after the special "ñ" Eg. "striñg" the result is "stri". – Danny Oct 17 '10 at 10:24
@Danny what if you disable the `ucwords()` and `strtolower()` for a moment? Any change? – Pekka Oct 17 '10 at 10:25
@Pekka, That works!!! So how can I fix this? I still want to use capital in the first letter of each word. – Danny Oct 17 '10 at 10:28
Alright, thanks Pekka, I'm gonna try to fix this and report here if something else happens, thanks :-) – Danny Oct 17 '10 at 10:38

score 0 · Answer 2 · answered Mar 01 '13 at 14:25

0

just use mysql_real_escape_string() no need to do anything else. for example: mysql_real_escape_string($user), mysql_real_escape_string($password));

answered Mar 01 '13 at 14:25

A H K

1,758
17
29

@A H K Is this true? – Ingus Jun 19 '17 at 14:16

score 0 · Answer 3 · edited Nov 23 '11 at 03:33

0

Why are you so worried about how your data is displayed in phpmyAdmin? The important thing is that you get the right display when you show them again in a html page.

you may eventually get correct display in database but then you may have wrong display on html

But i think set names do the job, it did for my similar problem even with string

edited Nov 23 '11 at 03:33

Peter O.

32,158
14
82
96

answered Mar 06 '11 at 19:13

Ahmad Pouramini

11
2

1

Ahmad the problem with the strange characters also affects queries in the database, so (for me) it's important to display the data correctly for the user and me. Multybyte string functions fixed the problem for me. – Danny Mar 07 '11 at 05:35

HTML form with spanish characters displayed wrong in MySQL database

3 Answers3