Special characters, MS SQL Server 2012, ASP and SQL?

Question

I have a folder, inside this folder I have a single file with this file name:

male♂.txt

I want to put this file name into my SQL Server 2012 database using ASP (VBScript):

<%@language=VBScript codepage=65001%><%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQL Server Native Client 11.0;Server=localhost;Database=databank;Uid=myuser;Pwd=mypass;"
Set RecSet = Server.CreateObject("ADODB.Recordset")
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each file In FSO.GetFolder("C:\inputs\").files
    RecSet.Open "SELECT * FROM filenames WHERE fname='"& file.name &"';", Conn, 0, 3
        IF RecSet.EOF THEN
            RecSet.AddNew
                RecSet("fname") = file.name
            RecSet.Update
        end IF
    RecSet.Close
    RecSet.Open "SELECT * FROM filenames WHERE fname='"& file.name &"';", Conn, 0, 3
        Response.Write "Is EOF: "& RecSet.EOF
    RecSet.Close
Next
Conn.Close
'setting variables to Nothing here
%>

After I run this code I check the database table using MS SQL Management Studio and there is indeed a post with "male♂.txt" in it.

The problem is that the ASP page outputs "Is EOF: True" and if I run the same script again another post with "male♂.txt" in it is created in the database.

I've recently moved from MS Access to MS SQL Server 2012 and the code above worked as intended on MS Access (EOF is false). What am I doing wrong?

Figured it out, need to add N before strings in SQL. Would post answer but stackoverflow has a retarded system where new accounts cant post answers until 8 hours after they have posted their question. Utterly retarded. I will return in some hours and post the answer if I remember to. — user3435078, Mar 19 '14 at 14:00
Try posting your answer now - 16 reputation ought to be enough to remove "new user" restrictions. (Other SE sites don't have the 8 hour restriction, though, so I might be wrong.) — Martha, Mar 19 '14 at 17:15
The technique used to build this sql code is vulnerable to sql injection attacks :( — Joel Coehoorn, Mar 19 '14 at 21:04
@JoelCoehoorn, that's true if and only if (1) `file.name` comes from user input, AND (2) this code is being run on the internet. From what I've gathered from this user's questions, the code is being run on localhost, and `file.name` doesn't look like something you'd get from a form. — Martha, Mar 19 '14 at 22:18
Was going to mention that I knew about possible SQL Injection but thought "nah, nobody will mention it". I was wrong. Well, change file.name with Replace(file.name,"'","''") in the SQL and it becomes safe from injection attacks. If the dark side of me ever awakens... — user3435078, Mar 20 '14 at 01:18
Does what? Becomes safe? If not please tell me what more characters I need to escape to make it safe. — user3435078, Mar 21 '14 at 12:11
@user3435078 Absolutely it's not safe and escaping characters isn't the best approach to fix it. You should be looking to use `ADODB.Command` which uses strongly typed parameters which avoid the need for "escaping" characters as the database knows what it is receiving and handles any data types accordingly. Some examples [SQL insert into database with apostrophe](http://stackoverflow.com/a/22037613/692942), [Using Stored Procedure in Classical ASP .. execute and get results](http://stackoverflow.com/a/21944948/692942) will both help introduce techniques you will find benefical in the long run. — user692942, Mar 21 '14 at 12:53

score 2 · Accepted Answer · answered Mar 19 '14 at 20:19

2

Seems like you always have to add "N" before strings in MS SQL Server for it to be read as Unicode.

If I change the two SQL statements to this it works:

SELECT * FROM filenames WHERE fname=N'"& file.name &"';

I didn't know this was needed. It worked without N in MS Access.

This is true for UPDATE and INSERT INTO as well, otherwise the unicode characters will turn out as questionmarks.

answered Mar 19 '14 at 20:19

user3435078

329
1
2
10

The N prefix before the start of a string in T-SQL tells SQL Server to treat the string as `NVARCHAR` which is the Unicode equivalent of `VARCHAR`. Don't forget to mark your answer as accepted (green tick). – user692942 Mar 20 '14 at 07:45
Didn't mean to cause offence, personally that kind of reaction isn't going help any though. The delay I believe is there to stop rep farming, once you earn some more rep you won't have this issue. You can always look at [meta stackoverflow](http://meta.stackoverflow.com) for more information or even contribute in a constructive way. – user692942 Mar 20 '14 at 21:30

Special characters, MS SQL Server 2012, ASP and SQL?

1 Answers1