php - Can't select a column name containing accent

one text

Problem

I'm actually on a new website project with Symfony 5. As I have to access an historical database where all of our products details are stored, I have to use AdoDb so I can use odbc driver (As doctrine doesn't allow to use it).

When I try selecting a field with accent, the query failed and throw a S0022 error code. Full error message is : "[Simba][SQLEngine] (31750) Column not found: ar1_h?�ndlerpreis"

See that ar1_h?�ndlerpreis contain a "?�" character which seems to be the problem.

What I have already tried

Here is my code for the query. Look at the ar1_h?�ndlerpreis which is present into the "Prepare" function first parameter.

$strIn = "";// Adodb odbc doesn't support IN(?) with an array parameter (string are quoted automatically)
            foreach ($array_article_number as $number) {
                $strIn .= "?,";
            }
            $strIn = substr($strIn, 0, -1); // Last one doesn't need ',' characters as it's the last one.

$query = $this->_db->Prepare("SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(" . $strIn . ")");
$datas = $this->_db->Execute($query, $array_article_number);

I have try to edit the field ar1_h?�ndlerpreis in my query string as :

ar1_h[?�]ndlerpreis (As told here)
`ar1_h?�ndlerpreis` (Surrounding with backstick ... Since I know that some fields need to be escaped)
ar1_h?�ndlerpreis || '' (As explain here it may have convert the column name to an expression an should not generate error)
ar1_h?�ndlerpreis + 0 (Same as previous one, it may have been converted to an expression)

I have also tried to change encoding character for accessing database :

$this->_db->setConnectionParameter('CharacterSet','UTF-8'); (As explain here)

But nothing do...

Some code

I provide you more code to see if i did something wrong.

This is my ADOdbConnection Service which i use when i need to connect to the historical database. Most of the time, i use Doctrine for the website specific database.

<?php

namespace App\Services;

use ADOConnection;

class ADOdbConnection
{
    private ADOConnection $_db;
    /**
     * AdodbConnection constructor.
     * @param $host
     * @param $user
     * @param $pwd
     * @param $db_name
     */
    public function __construct($host, $user, $pwd, $db_name)
    {
        $this->_db = ADONewConnection('odbc');
        $this->_db->setConnectionParameter('CharacterSet','UTF-8');
        $this->_db->connect('DRIVER={CONZEPT 16 ODBC-Treiber (64 bit)};SERVER='.$host.';DB='.$db_name.';', $user, $pwd, $db_name);
    }

    public function getDb() : ADOConnection{
        return $this->_db;
    }

}

It work as it should, doing a connection to the database.

Then, this is my DAL for the product (Article) table :

<?php

namespace App\Services\DAL_w2;

use ADOConnection;
use App\Services\ADOdbConnection;

class ArticleDal
{

    private ADOConnection $_db;

    public function __construct(ADOdbConnection $ADOdbConnection)
    {
        $this->_db = $ADOdbConnection->getDb();
        $this->_db->setCharset('UTF-8');
        $this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
    }

    /**
     * Join w2 database for every article number given
     * @param array $array_article_number An array of article number.
     * @return array|null An array of article from w2 article's table or null
     */
    public function getDataForListArticleFromArticleNumber(array $array_article_number): ?array
    {
        $res = null;
        if (count($array_article_number)) {
            $strIn = "";// Adodb doesn't support IN(?) with an array parameter (string are quoted automatically)
            foreach ($array_article_number as $number) {
                $strIn .= "?,";
            }
            $strIn = substr($strIn, 0, -1); // Last one doesn't need ',' characters as it's the last one.
            $query = $this->_db->Prepare("SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis + 0 AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(" . $strIn . ")");
            dump($query);
            $datas = $this->_db->Execute($query, $array_article_number);
            dump($this->_db->_errorCode);
            dump($this->_db->_errorMsg);
            dump($datas);
            die();
            // Assign article number as array key
            $res = array();
            foreach ($datas as $article_data) {
                $res[$article_data['ar1_artikelnummer']] = $article_data;
            }
        }
        $res = $res ?: null; // If we don't have any data
        return $res;
    }

}

This code display the following result :

ArticleDal.php on line 37: "SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis + 0 AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "

ArticleDal.php on line 39: "S0022"

ArticleDal.php on line 40: "[Simba][SQLEngine] (31750) Column not found: ar1_h?�ndlerpreis"

ArticleDal.php on line 41: false

And there is an example of what return a SELECT * (I truncate it because it have 360 columns...) :

 0 => array:360 [?�?
    "ar1_artikelnummer" => "0002446"
...
   "ar1_preislistenartik" => "0"
    "ar1_preislistenpreis" => "0"
    b"ar1_preislisten?�nder" => null
    "ar1_preislistenprneu" => "0"
    "ar1_preislistenneuab" => null
    "ar1_collianz" => "1"
    "ar1_tiefstpreis_brut" => "0"
    "ar1_ean_nummer" => ""
    "ar1_faxpreislistenar" => "0"
    "ar1_inetrnetartikel" => "0"
    "ar1_unterwarengruppe" => "1"
    "ar1_verbot_in_stl" => "1"
    "ar1_apothekenpreis" => "5.82"
    b"ar1_h?�ndlerpreis" => "3.77"
...

It's weird those little 'b' in front of field with accent (For ar1_h?�ndlerpreis and ar1_preislisten?�nder). dump() is the debug function from Symfony framework.

If i use var_dump instead of dump, i got this result :

["ar1_h???ndlerpreis"]=> string(4) "3.77"

Thank you.

Source