How to convert a float to a string regardless of regional settings?

Question

My product is targeted to a Portuguese audience where the comma is the decimal symbol. I usually use CString::Format to input numbers into strings, and it takes into account the computer's regional settings. While in general this is a good approach, I'm having problems in formatting SQL queries, for instance:

CString szInsert;
szInsert.Format("INSERT INTO Vertices (X, Y) VALUES (%f, %f)", pt.X, pt.Y);

When values are passed I get this string which is an incorrect query:

INSERT INTO Vertices (X, Y) VALUES (3,56, 4,67)

How do I enforce the dot as the decimal symbol in these strings, without changing the regional settings and without having to make specialized strings for each float value?

Note: this is intended as a general question, not a SQL one.

Answer 1

Bad idea, you really should be using prepared statements. It's not really trivial to do SQL injection with just numbers, but CString::Format is just not the correct way to do parameter binding.

(MFC and SQL has been a while - turns out this is bloody well hidden. I'm starting to see how we ended up with SQL injection bugs, thanks Microsoft. With raw ODBC you create a statement (once) with SQLPrepare. Pass ? for the 2 parameters you want to fill in. Subsequently, for each INSERT call SQLBindParameter(stmt, 1, &X); SQLBindParameter(stmt, 2, &Y) /*extra parameters omitted, see http://msdn.microsoft.com/en-us/library/ms710963(VS.85).aspx */. Finally, call SQLExecute to preform the operation. )

Answer 2

Parameterized queries should avoid this issue altogether. You should look into those. That said, you should be able to use setlocale or similar to change the decimal separator.

Answer 3

A comment about Pukku's suggestion with ostringstream: For this to be locale-independent, one should explicitely imbue() the stream with the desired locale:

std::ostringstream s;
s.imbue(std::locale::classic());
s << "INSERT INTO Vertices (X, Y) VALUES (" << pt.X << ", " << pt.Y << ")";

Otherwise, the current global locale is used.

Answer 4

Here's what I did.

CString FormatQuery(LPCTSTR pszFormat, ...)
{
    CString szLocale = setlocale(LC_NUMERIC, NULL);
    setlocale(LC_NUMERIC, "English");

    va_list args;
    va_start(args, pszFormat);
    CString szFormatted;
    int nSize = (_vscprintf(pszFormat, args) + 1) * sizeof(char);
    _vsnprintf_s(szFormatted.GetBuffer(nSize), nSize, nSize, pszFormat, args);
    szFormatted.ReleaseBuffer();
    va_end(args);

    setlocale(LC_NUMERIC, szLocale);
    return szFormatted;
}

You should use it like sprintf. You must #include <locale.h> in order for it to work.

I'm a bit stubborn so I didn't use prepared statements/parametrized queries. If you have a similar problem, I suggest you do that. Meanwhile, if your problem is not SQL-related, my answer should help.

Edit: Here's a thread safe version:

CString FormatQuery(LPCTSTR pszFormat, ...)
{
    _locale_t locale = _create_locale(LC_NUMERIC, "English");

    va_list args;
    va_start(args, pszFormat);
    CString szFormatted;
    int nSize = (_vscprintf_l(pszFormat, locale, args) + 1) * sizeof(char);
    _vsnprintf_s_l(szFormatted.GetBuffer(nSize), nSize, nSize, pszFormat, locale, args);
    szFormatted.ReleaseBuffer();
    va_end(args);

    return szFormatted;
}