Surplus 'N' in front of string param

[TumaMilan]

Surplus 'N' in front of string param

[milo]

Hm, I've commented it this afternoon but comment is missing - maybe didn't submit the form.

You wrote ...escaping of escaped text does waste... thats corrent. If you are 100% sure that SQL injection cannot occure, e.g. the SQL string cames from $sql = $connection->translate(), you should use %SQL modifier:

$sql = $db->translate('SELECT * FROM table WHERE ? = ?', 'A', 'A');
$db->query('%SQL', $sql);  # dibi does not touch it

But must be 100% sure, otherwise it's an SQL injection gateway.

[JanTvrdik]

Unfortunately I can't make it work by not using 'N'. The query is part of contribute datagrid

Can you link the specific query that's causing problem?

[TumaMilan]

If you are 100% sure that SQL injection cannot occure, e.g. the SQL string cames from $sql = $connection->translate(), you should use %SQL modifier:

I cannot do any changes to source of Ublaboo datagrid. The algorythm of questioning is automatic. The query is translated twice. Issue is only on combination UblabooDatagrid->Dibi Fluent->MSSQL

[TumaMilan]

Ublaboo datagrid

Sorry, Ublaboo datagrid renamed to Contribute datagrid. But not totally, so it is little confusing 😁

[TumaMilan]

Can you link the specific query that's causing problem?

I'm afraid I don't understand you. If you mean SQL query, so any query containing apostrophe. Any text value. Datagrid does 2 translations of query, second pass destroys the query. I can't influence this.

[TumaMilan]

I tried to change the regex and callback in Translator.php to identify N before apostrophe. But it is not easy and can't guarantee full functionality of function translate(). I decided to extend class SqlsrvDriver with my SqlsrvsitmpDriver:

<?php
declare(strict_types=1);

namespace Dibi\Drivers;

class SqlsrvsitmpDriver extends SqlsrvDriver implements \Dibi\Driver
{
    public function escapeText(string $value): string
    {
        return "'" . str_replace("'", "''", $value) . "'"; // N removed
    }
}

and in configuration use

dibi:
	driver: sqlsrvsitmp

Removing N could be parametric - for example by new item in dibi options (UseNvarchar = false) in configuration. This setting would consume __construct() method of driver class and pass it to method escapeText(string $value) by local field. But for my use case is it not necessary.
Milan Tůma