php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #69348 mysql_real_escape_string produces invalid decimal numbers on some locales
Submitted: 2015-04-01 14:19 UTC Modified: 2017-09-22 19:11 UTC
From: mdrolc at gmail dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 5.6.7 OS: All
Private report: No CVE-ID: None
 [2015-04-01 14:19 UTC] mdrolc at gmail dot com
Description:
------------
SQL only accepts floats with dot as the decimal separator. But running a float through mysql_real_escape_string() returns a string with comma as the decimal separator on some locale configurations. MySQL fails silently and just cuts off the decimal part.

This behaviour can result in nasty hard to catch bugs because behaviour depends highly on server configuration.

mysql_real_escape_string() should always cast types according to MySQL conventions which in this case means that decimal numbers should use dot as a decimal separator regardless of the locale setting.

Test script:
---------------
mysql_connect("localhost", "root", "dev");

//English, everything is fine
setlocale(LC_ALL, 'en_US.UTF8');
var_dump(localeconv()['decimal_point']); //string(1) "."
var_dump(mysql_real_escape_string(19.9)); //string(4) "19.9"

//Slovene, (Use a locale that is installed on your system to successfully reproduce this)
setlocale(LC_ALL, 'sl_SI.UTF8');
var_dump(localeconv()['decimal_point']); //string(1) ","
var_dump(mysql_real_escape_string(19.9)); //string(4) "19,9"



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-04-01 18:50 UTC] stas@php.net
-Status: Open +Status: Not a bug
 [2015-04-01 18:50 UTC] stas@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

Doesn't look like a mysql bug. See:

setlocale(LC_ALL, 'sl_SI.UTF8');
var_dump((string)19.9);

It's just how string conversion of floats works.
 [2017-09-22 18:57 UTC] seb35 at seb35 dot fr
I disagree this is not a bug: mysqli_real_escape_string is supposed to convert PHP literals to SQL literals, and the representation of the floating number 0.1 in SQL is not "0,1" but "0.1" (the result is really a string and not a PHP float which would then converted to a string during output in the test script). Below a test script to show the result of mysqli_real_escape_string is "0,1" and it does not result of some processing during output.

As documented, the charset is relevant for the strings, but this is about the decimal mark and not the charset.

I experienced this issue in PHP 5.6.30, 7.0.23, 7.1.9, 7.2.0RC2.

I guess this is linked to bug #74779, where the result of an SQL statement implying a float is trucated to the integer part, possibly because of a wrong decimal separator.

Test script:
---------------
setlocale( LC_NUMERIC, 'fr_FR.UTF-8' );
$conn = new mysqli();
$number = 1/10;
$result = $conn->real_escape_string( $number );
setlocale( LC_NUMERIC, 'C' );
var_dump( $result );

Expected result:
----------------
string(3) "0.1"

Actual result:
--------------
string(3) "0,1"
 [2017-09-22 19:11 UTC] mdrolc at gmail dot com
The thing is that real_escape_string is declared like this (copied from PHP manual):

string mysqli::real_escape_string ( string $escapestr )


The consequence is that the following are equal:
$result = $conn->real_escape_string( $number );
$result = $conn->real_escape_string( (string) $number );

A more robust and probably the best way to insert parameters into SQL statements is probably usage of prepared statements + mysqli_stmt_bind_param. mysqli_stmt_bind_param can actually distinguish between integer/decimal/string/binary types in contrast to mysqli_real_escape_string.
 [2017-09-22 19:14 UTC] spam2 at rhsoft dot net
all that examples are plain wrong code to start with - RTFM - the data type for the param is STRING and NOT FLOAT (besides that no one should use functions prefixed with mysql_ for many years now)

in PHP7 with strict-types this would simply raise an exception and without strict-types THE CALLER and NOT the receiver does implicit casting

so the function for which you wrote the Bugreport has no business at all with your problem
 [2017-09-22 19:58 UTC] seb35 at seb35 dot fr
Indeed, I misread, sorry about that and thanks for the quick answers. I will then open a bug in the software I experienced the bug (MediaWiki).
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Oct 21 19:03:35 2021 UTC