Problem with Persian/Arabic characters when using input()

[ShahriarKh]

I noticed that there's a problem with Persian/Arabic characters when using .input().

[!NOTE] Both ی (Persian) and ي (Arabic) represent the ~same character but with different unicodes. In my database, since there isn't strict formatting rules, both forms exist; for example, both 'شادی' and 'شادي' are possible and acceptable values.

When I directly write the value in my query, it works as desired and both values return data.

// ✅ Works
data = await pool.request().query(`select Name as first_name from People where Name = 'شادی'`);

// ✅ Works
data = await pool.request().query(`select Name as first_name from People where Name = 'شادي'`);

Using JS variables works too:

// ✅ Works
const PERSIAN = 'شادی';
data = await pool.request().query( `select Name as first_name from People where Name = '${PERSIAN}'`);

// ✅ Works
const ARABIC = 'شادي';
data = await pool.request().query( `select Name as first_name from People where Name = '${ARABIC}'`);

But when I use input() to utilize sql variables, only the Arabic text works:

// ✅ Works
data = await pool.request()
  .input('nameVar', NVarChar(20), 'شادي')
  .query(`select Name as first_name from People where Name = @nameVar`);

// 🔴 Doesn't Work
data = await pool.request()
  .input('nameVar', NVarChar(20), 'شادی')
  .query(`select Name as first_name from People where Name = @nameVar`);

Seem like there's an issue with input() and how it passes data to query, because using variables with both values work in sql (direct query)

-- ✅ Works
declare @nameVar nvarchar(20) = 'شادی'
select
  name
from
  people
where
  name = @nameVar

-- ✅ Works
declare @nameVar nvarchar(20) = 'شادي'
select
  name
from
  people
where
  name = @nameVar

Expected behaviour:

input() should behave like direct queries and don't change characters or anything.

Software versions

• NodeJS: v18.18.2
• node-mssql: v10.0.1
• SQL Server: 15.0.2000.5

[dhensby]

How interesting. Can you confirm what you mean by "doesn't work"?

Do you have a minimal set queries / DB structure & data that I can use to replicate the issue?

[ShahriarKh]

Doesn't work = returns 0 rows. My database has ARABIC_CI_AS collation, and although it isn't desired, I can't easily change it.

If you want to test, here's a simple query to create the db:

CREATE DATABASE PersianTest
COLLATE ARABIC_CI_AS

USE PersianTest
CREATE TABLE People (ID INT, Name NVARCHAR(20))

INSERT INTO PersianTest.dbo.People (ID, Name) VALUES (1, 'شادی'), (2, 'شادي'), (3, 'علی'), (4, 'علي')

[ShahriarKh]

The same thing happens for procedures:

-- using varchar
CREATE PROCEDURE TestPersian @inputField VARCHAR(255) AS BEGIN
SELECT
  Name
FROM
  People
WHERE
  Name = @inputField;
END
GRANT EXEC ON dbo.TestPersian TO PUBLIC

-- using nvarchar
CREATE PROCEDURE NTestPersian @inputField NVARCHAR(255) AS BEGIN
SELECT
  Name
FROM
  People
WHERE
  Name = @inputField;
END
GRANT EXEC ON dbo.NTestPersian TO PUBLIC

Using sql directly:

/* persian text */
EXEC TestPersian 'علی'; -- ✅
EXEC NTestPersian 'علی'; -- ✅
EXEC TestPersian N'علی'; -- ✅
EXEC NTestPersian N'علی'; -- ❌

/* arabic text */
EXEC TestPersian 'علي'; -- ✅
EXEC NTestPersian 'علي'; -- ✅
EXEC TestPersian N'علي'; -- ✅
EXEC NTestPersian N'علي'; -- ✅

Using js:

/* arabic text */
data = await pool.request().input('inputField', VarChar, 'علي').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', VarChar, 'علي').execute('NTestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علي').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علي').execute('NTestPersian'); // ✅

/* persian text */
data = await pool.request().input('inputField', VarChar, 'علی').execute('TestPersian'); // ❌
data = await pool.request().input('inputField', VarChar, 'علی').execute('NTestPersian');  // ❌
data = await pool.request().input('inputField', NVarChar, 'علی').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علی').execute('NTestPersian'); // ❌

[dhensby]

OK - this seems to be a problem either with the underlying tedious driver, or SQL as whole. I've just created this test script making use of raw tedious driver and get the same behaviour as I do with node-mssql:

const { connect, Request, TYPES } = require('tedious');

function doConnect () {
  return new Promise((resolve, reject) => {
    const connection = connect({
      server: 'localhost',
      options: {
        encrypt: true,
        database: 'PersianTest',
        trustServerCertificate: true,
        rowCollectionOnRequestCompletion: true
      },
      authentication: {
        type: 'default',
        options: {
          userName: 'sa',
          password: 'yourStrong(!)Password'
        }
      }
    }, (err) => {
      if (err) { reject(err) } else { resolve(connection) }
    })
  })
}

(async () => {
  const names = ['شادی', 'شادي', 'علی', 'علي']
  const res = await Promise.all(names.map((name) => {
    return new Promise(async (resolve, reject) => {
      const conn = await doConnect()
      const request = new Request(`SELECT * FROM [People] WHERE [Name] = @name`, (err, count, rows) => {
        conn.close()
        if (err) {
          reject(err)
        } else {
          resolve(rows.map((cols) => {
            return cols.reduce((acc, col) => ({
              ...acc,
              [col.metadata.colName]: col.value
            }), {})
          }, []))
        }
      })
      request.addParameter('name', TYPES.NVarChar, name)
      conn.execSql(request)
    })
  }))
  console.log(res)
})().then(() => {
  console.log('Done')
}).catch(console.error)

Output:

[
  [],
  [ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
  [],
  [ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ]
]

If I don't use a parameter, it works as expected:

const { connect, Request, TYPES } = require('tedious');

function doConnect () {
  return new Promise((resolve, reject) => {
    const connection = connect({
      server: 'localhost',
      options: {
        encrypt: true,
        database: 'PersianTest',
        trustServerCertificate: true,
        rowCollectionOnRequestCompletion: true
      },
      authentication: {
        type: 'default',
        options: {
          userName: 'sa',
          password: 'yourStrong(!)Password'
        }
      }
    }, (err) => {
      if (err) { reject(err) } else { resolve(connection) }
    })
  })
}

(async () => {
  const names = ['شادی', 'شادي', 'علی', 'علي']
  const res = await Promise.all(names.map((name) => {
    return new Promise(async (resolve, reject) => {
      const conn = await doConnect()
      const request = new Request(`SELECT * FROM [People] WHERE [Name] = '${name}'`, (err, count, rows) => {
        conn.close()
        if (err) {
          reject(err)
        } else {
          resolve(rows.map((cols) => {
            return cols.reduce((acc, col) => ({
              ...acc,
              [col.metadata.colName]: col.value
            }), {})
          }, []))
        }
      })
      conn.execSql(request)
    })
  }))
  console.log(res)
})().then(() => {
  console.log('Done')
}).catch(console.error)

Output:

[
  [ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
  [ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
  [ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ],
  [ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ]
]

Would you mind opening an issue with the tedious driver directly?