php, MySQL and variables containing å, ä and ö escape problem

Question

Hi. I'm having some problem with my SQL syntax/escaping variables on my LAMP server.

The command I want to use is the following:

$sql=mysql_query("INSERT INTO '$table' (FirstName, LastName, StartDate, TimeStroke, DueDate, Duration, Price, Retailer, Checksum)
VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[startdate]','$_POST[timestroke]','$duedate','$_POST[duration]','$price','$_SESSION[name]','$random')");

The problem is that sometimes the $table variable contains characters like å, ä and ö. Hence I need to put ' ' around $table to make sure it stays the same. However when doing that recieve the error:

"Error: 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 ''tablename' (FirstName, LastName, StartDate, TimeStroke, DueDate, Duration, P' at line 1".

Looks like the escaping by ' ' creates a problem. I've tried with replacing the query with a mysql_real_escape_string:

"$sql=sprintf("INSERT INTO '".mysql_real_escape_string($table)."' (FirstName, [...]" 

but that doesnt help me either. Is there a way to keep the data in the variable intact and still be able to run the query? Or do I have to accept that å,ä,ö is banned from php/MySQL?

Answer 1

This is do to with character-encoding. Check out http://www.sitepoint.com/blogs/2006/03/15/do-you-know-your-character-encodings/

---

Put header('Content-Type: text/html; charset=utf-8'); at the top of your page

Also try doing mysql_set_charset('utf8'); before insert/reading from DB. Then you should put the following on your form that's posting to your PHP file:

<form action="/your-post-controller.php" method="post" accept-charset="utf-8">

Notice the accept-charset="utf-8 -- this is extremely important otherwise your header will report to the PHP file its in latin1

It should work then.

Also take a look at http://www.phpwact.org/php/i18n/charsets -- was trying to find the link, definitely worth a read for anyone interested in getting character encoding right, see the Iñtërnâtiônàlizætiøn string for testing your PHP&MySQL table

Answer 2

You would use backticks (`) to surround the table name. Nothing to do with Character-encoding:

$sql=mysql_query("INSERT INTO `$table` (FirstName, LastName, StartDate, TimeStroke, DueDate, Duration, Price, Retailer, Checksum)
VALUES ('{$_POST['firstname']}','{$_POST['lastname']}','{$_POST['startdate']}','{$_POST['timestroke']}','$duedate','{$_POST['duration']}','$price','{$_SESSION['name']}','$random')");

A couple of side notes here:

A: you should really user mysql_real_escape_string on any input coming in from an unknown source to avoid someone destroying your database with SQL Injection.

B: You should use ' around array associative indexes, reason being is that these all would throw notice undefined constant (or something to that matter) errors. Which will fill up your error log and make finding more critical errors a bit harder if you ever need to go back.