1

I have a Table named "status" Which is used to store objects status. The table has 5 columns and except "symbol" column which it's type is VARCHAR utf8mb4_unicode_ci, All the others are just typical integers.

The problem is that because the symbol column can store English and Non-English characters, It returns NULL(With var_dump($row)) When I use my php file "getStatus.php" to retrieve the data from a row with Non-English symbol but It works well when I use English characters as symbol.

This is my table(the symbol on the second row is "تست"):

my table

This is the php code, "getStatus.php" :

if(!isset($_GET["symbol"]))
        die("please specify the symbol");
$con = mysqli_connect("localhost","root","******","****");
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}
$query = "SELECT * FROM `status` WHERE `symbol` =  '" . $_GET["symbol"] . "';";
$result = mysqli_query($con,$query);
if (!$result)
        die("unable to retreive status");
$row = mysqli_fetch_assoc($result);
if ($row["status"] == 1)
        echo $row["percentage"];
else
        echo -1;

I can successfully get data When I execute getStatus.php?symbol=EU which calls mysql with 

SELECT * FROM `status` WHERE `symbol` = 'ER';

But When I execute getStatus.php?symbol=تست, I got null! The confusing point is that if I copy the query and enter it in mysql command line It will work but it doesn't work when I send the same query with mysqli in php!!

SELECT * FROM `status` WHERE `symbol` = 'تست';

Why it works in mysql commandline and phpmyadmin but not with mysqli?

omidh
  • 2,526
  • 2
  • 20
  • 37
  • Use 4 space indentation for code blocks rather than `>` blockquote formatting. That will preserve all your code and apply syntax highlighting correctly. – Michael Berkowski Dec 29 '15 at 15:15
  • add this after you connect (mysql_query("SET NAMES 'utf8'"); mysql_query('SET CHARACTER SET utf8'); ) --- source http://forums.mysql.com/read.php?103,209072,209072 – Tasos Dec 29 '15 at 15:17
  • You probably need to `urlencode()` that value before requesting it in the query string. If you are submitting this from a form, that should be automatic, but if you are testing it manually, you would need to do it as `getStatus.php?symbol=%D8%AA%D8%B3%D8%AA` PHP will then automatically decode it. – Michael Berkowski Dec 29 '15 at 15:17
  • 1
    You have a serious SQL injection problem though, using `$_GET['symbol']` directly in the query. Review the mysqli `prepare()/bind_param()/execute()` examples on [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php), but at a minimum, you _must_ call `mysqli_real_escape_string($con, $_GET['symbol'])` to sanitize that value. – Michael Berkowski Dec 29 '15 at 15:19
  • you are using msqli so check here -- http://stackoverflow.com/questions/10829816/php-set-character-names-using-mysqli – Tasos Dec 29 '15 at 15:19
  • @Tasos Non of them worked. – omidh Dec 29 '15 at 15:23
  • @MichaelBerkowski I used the manual form getStatus.php?symbol=%D8%AA%D8%B3%D8%AA But it didn't worked – omidh Dec 29 '15 at 15:23
  • Then you may have client encoding problems as another commenter suggested. – Michael Berkowski Dec 29 '15 at 15:25
  • @MichaelBerkowski I did what Tasos suggested but it didn't work, Why it works with mysql cmd if it has encoding problems – omidh Dec 29 '15 at 15:27
  • 1
    its probably the web server brand you have like here that has the problem cause those solutions should work. http://stackoverflow.com/questions/21604657/arabic-character-on-php-and-mysql – Tasos Dec 29 '15 at 15:27
  • Dont use the curency name as get value, switch to using mysql table ids. So that getStatus.php?symbol=تست becomes getStatus.php?symbol=14. Adjust your sql code accordingly – maxhb Dec 29 '15 at 15:36
  • @Tasos This one worked!!! – omidh Dec 29 '15 at 15:37

0 Answers0