1

The service on my Windows Server using MS-SQL is using a variety of languages but non-English characters like Chinese and Arabic are recorded as ??????????????

The database fields are set to [nvarchar] and the charset is UTF-8.

Web searches and the posts here mostly recommend:

insert into @table select N'你好'

But how I do use the 'N' option, how to include it here?

SQL = "Insert Into Table ( [Title] ) Values ( '" & strTitle & "' )"
dbConnection.Execute(SQL)

All answers suggesting the N switch do not actually give an example of how it should be used.

NOTE: the suggestion using of PARAMETERS is not related to this question at all. SQL injection is not possible here as all form inputs are sanitised and the service is local only. Tangential advice is not welcome.

Kendo
  • 149
  • 9
  • 1
    Write good (and secure) code and use parameters and this issue disappears, as you can *easily* define your parameters as an `nvarchar`. – Thom A Jun 19 '22 at 20:40
  • 1
    _how to include it here?_ The same way you include the single quote character as the string delimiter. But don't - use parameterized queries. – SMor Jun 19 '22 at 21:06
  • 1
    "apparently that is not suitable for English characters" is incorrect too. The `N` prefix will work fine for all characters but you should use parameters not your SQL injection vulnerable string concatenation method. – Martin Smith Jun 19 '22 at 21:13
  • 1
    Whoover marked this question as being similar to another needs to read both more carefully. The recommended article has nothing to do with this problem at all. – Kendo Jun 20 '22 at 00:26
  • 1
    &larnu all form input is santised and the columns are already set to NVARCHAR. – Kendo Jun 20 '22 at 00:28
  • 1
    @MartinSmith I should have mentioned that all form input is sanitised. – Kendo Jun 20 '22 at 00:29
  • 1
    @Smor No need to use parameters in this instance. It would be nice to see an example of N included in my code. – Kendo Jun 20 '22 at 00:31
  • 3
    The recommended article does solve the problem. If you use parameters of type nvarchar you won't get this issue with question marks. This is the correct solution. You shouldn't be building up queries with literal values concatenated into them and no need to "sanitise" the values then whatever that means in your case. You can assign arbitrary values to parameters as they will always be treated as data not executable code. – Martin Smith Jun 20 '22 at 06:27
  • *"and the columns are already set to NVARCHAR."* And if you are passing **`varchar`** values to that column, then it's pointless it being an `nvarchar` (which is what you are doing here). Again, parametrise, it is both the correct solution and the solution you need. – Thom A Jun 20 '22 at 07:36
  • Perhaps you missed line #3 which does clearly state that NVARCHAR is already used. Paramaterising is not a solution here and the other question does not even mention non-English language problems at all. Maybe you should read that one again too. – Kendo Jun 20 '22 at 13:15

0 Answers0