0

I have a Stored Procedure here:

USE [RK]
GO
/****** Object:  StoredProcedure [dbo].[PRC_THEKE_FRAGE_INSERT]    Script Date: 03/09/2017 17:43:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[PRC_THEKE_FRAGE_INSERT] @frage nvarchar(255),@status bit, @kategorieID tinyint, @sgi nvarchar(8)
AS

-- Neuen Datensatz speichern
INSERT INTO LLThekeFragen SELECT @frage, @status, @kategorieId, @sgi

This procedure works fine, unless I have umlauts in my data. Then it is messing up the input. For instance, it looks like this after the insert:

Begrüßung

This is obviously wrong. I tried to check the MSDN-Page here, which in fact describes the problem. However, not in a stored procedure. I also tried to add N' to my variables, but that obviously lead to having a string (instead of the actual value).

My current charset for the table is: SQL_Latin1_General_CP1_CI_AS

I tried changing the type for frage to varchar(255), which didn't help, removed the N' again.. but nothing did the trick. While checking the data in my browser, the SQL-statement is correct. It mus be while inserting it.

This is how I try to insert my data in asp-classic

SQL = "EXEC PRC_THEKE_FRAGE_INSERT '" & request("question") & "'," & request("active") & ", " & request("categoryID") & ", '" & request("sgi") & "'"
conn.execute SQL

(I know, not sanitizing right now and I shouldn't do this. Please don't tell that now :) )

Update

Header in my actual asp-file:

Very first line of my code:
<%@ LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>

in my <head>-area:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

File with my stored procedure:

'this indeed did the trick - had it in the wrong file
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>

<%
dim conn,SQL
set conn = Server.CreateObject("ADODB.Connection")

'open DB connection
conn.open "DSN=well;UID=rk;PWD=wellwell"

SQL = "EXEC PRC_THEKE_FRAGE_INSERT N'" & request("question") & "'," & request("active") & ", " & request("categoryID") & ", '" & request("sgi") & "'"
conn.execute SQL

conn.close()

%>
DasSaffe
  • 2,080
  • 1
  • 28
  • 67
  • what's frage datatype in LLThekeFragen? if it's not nvarchar, then your it'll never display correctly. – S3S Mar 09 '17 at 17:06
  • This has been answered before, just search on asp-classic and utf-8 should be enough. – user692942 Mar 09 '17 at 19:24

1 Answers1

3

Make sure that to include this the very top of your page.

<%@Language="VBScript" CodePage = 65001 %>
<% 
  Response.CharSet = "UTF-8"
  Response.CodePage = 65001
%>

or use an include file as described here: https://stackoverflow.com/a/21914278/2333499

Use a parameterized query with adVarWChar parameters
or open yourself up to sql-injection and just add N before your concatenating your strings. 
SQL = "EXEC PRC_THEKE_FRAGE_INSERT N'" & request("question") & "'," & request("active") & ", " & request("categoryID") & ", N'" & request("sgi") & "'"
conn.execute SQL
Community
  • 1
  • 1
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I'll doing that in asp. wait, I'll show you how I'm calling that – DasSaffe Mar 09 '17 at 17:09
  • 1
    @DasSaffe Are you using `SqlDbType.NVarChar` for your parameter type? – SqlZim Mar 09 '17 at 17:10
  • isn't that SqlDbType.NVarChar here? `ALTER PROCEDURE [dbo].[PRC_THEKE_FRAGE_INSERT] @frage nvarchar(255)` sorry, new to T-SQL + Microsoft SQL Server – DasSaffe Mar 09 '17 at 17:13
  • 1
    @DasSaffe Okay, well, do the thing that you told us not to tell you to do. – SqlZim Mar 09 '17 at 17:20
  • I can't see how that helps in this particular situation. But ofc you are right, this will be added sooner or later. But it doesn't help if I can't insert my data correctly, even though they are safe then – DasSaffe Mar 09 '17 at 17:22
  • Tried all of that, still not working, I'll update the question with the current suggestions you made – DasSaffe Mar 09 '17 at 17:34
  • 1
    Nvm @SqlZim, you were right. Had the codepage in the wrong file. Works as expected. thank you – DasSaffe Mar 09 '17 at 17:39
  • No don't use `N'` use a parameterised query. – user692942 Mar 09 '17 at 17:52
  • @DasSaffe after all that you were just missing the codepage declaration in the correct file. Don't prefix with `N'` use parameterised queries by utilising the `ADODB.Command` object. Don't use `` tags in HTML to set content type, make sure you are returning UTF-8 from the server by specifying `Response.Charset = "UTF-8"` and making sure your ASP file is saved as UTF-8 and with the `<%@codepage=65001%>` directive it is processed by ASP as UTF-8. The advice so far in this answer is poor to say the least. – user692942 Mar 09 '17 at 18:54
  • Also `SqlDbType.NVarChar` is the ADO.Net data type for `NVARCHAR` so no idea why this is mentioned here the equivalent in ADODB is the constant `adVarWChar`. – user692942 Mar 09 '17 at 18:56
  • @Lankymart OP said they would be switching to parameterized queries later. I mentioned `SqlDbType.NVarChar` because OP hadn't said they were using classic asp yet. The original question didn't even specify this was an issue originating from an application. Please look at the revisions made to the question before throwing me under the bus. – SqlZim Mar 09 '17 at 18:58
  • 1
    @SqlZim wasn't *"throwing you under a bus"*, if revisions have been made update the answer, because the "For asp classic" section isn't great. – user692942 Mar 09 '17 at 19:05
  • @Lankymart If you want to edit my answer to improve it, or post your own, please feel free. Your understanding of asp classic is much better than mine. – SqlZim Mar 09 '17 at 19:15
  • 1
    I've already written about this before, I'd just be repeating myself. – user692942 Mar 09 '17 at 19:20