VBScript Registration [ASP] MSSQL]

Question

Where is the problem?

Error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.

/checkregistrazione.asp, line 27"

The code is:

<%@LANGUAGE = VBScript%>
<%
Dim str_cn
str_cn = ""
str_cn = str_cn & "driver={SQL Server};Server=62.149.153.49;Database=MSSql151186;uid=MSSql151186;pwd=y5ii1s154j;"
Dim rs ' Variabile per il Recordset
' Creo un'istanza per la connessione ed una per il Recordset
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.Open str_cn ' Apro la connessione al database

Dim nomeutente
nomeutente = Request.Form("nomeutente")
Dim password
password = Request.Form("password")
Dim email
email = Request.Form("email")
Dim nomeimpresa 
nomeimpresa = Request.Form("nomeimpresa")
Dim cellulare 
cellulare = Request.Form("cellulare")

Dim SQL ' Creo la query SQL
SQL = "INSERT INTO [dbo].[utenti] ([ID_utente], [nome_utente], [password], [email], [nome_impresa], [cellulare]) VALUES ("& 2 &","& nomeutente &","& password &","& email &","& nomeimpresa &","& cellulare &");"

' Apro il Recordset
rs.Open SQL, cn
%>
<html>
<head>
<title>Lettura dei dati da un database</title>
</head>
<body>
<%
' Verifico che la tabella contenga dati
' Se non ne contiene lancio un messaggio di avviso
If rs.EOF = True Then
%>
<p>Errore</p>
<%
' Se invece ne contiene visualizzo i dati in funzione
' della query SQL specificata
Else
While rs.EOF = False
%>
    <p>
     <h1>Dati inseriti correttamente nel database</h2>
    </p>
<%
rs.MoveNext
Wend
End If
%>
</body>
</html>
<%
' Chiudo il Recordset
rs.Close
Set rs = Nothing
' Chiudo la connessione
cn.Close
Set cn = Nothing
%>

Please help me! The database is a MSSQL Server

Check this https://stackoverflow.com/questions/26750894/microsoft-ole-db-provider-for-sql-server-error-80040e14-incorrect-syntax-near — Rex, Aug 28 '17 at 14:07
Can you do 1 example with my code? Because the codes are different — Michele Dragonetti, Aug 28 '17 at 14:10
Do following two steps. First: Add <%=SQL%> underneath your variable SQL and print it to browser. Second: Copy the output of this query to Management console of SQL Server and figure out what the problem is. — Reporter, Aug 28 '17 at 14:11
Based on the above: every one of the character data type values is missing the opening and closing apostrophe's. numeric data doesn't require it, character data and dates do. Consider... `"& 2 &",'"& nomeutente &"','"& password &"','"& email &"'..` however paramaterized queries are much safer to use. and resolve issues such as if a name were to have a apostophe in it. — xQbert, Aug 28 '17 at 14:19

Erik Oosterwaal · Answer 1 · 2017-08-28T20:19:30.810

There is a syntax error in your query. Because you have variables in your query, there might be something in the variables that is messing up your query. When I look at your query, the most likely explanation is that your text fields need to be quoted, like this:

SQL = "INSERT INTO [dbo].[utenti] ([ID_utente], [nome_utente], [password], [email], [nome_impresa], [cellulare]) VALUES ("& 2 &",'"& nomeutente &"','"& password &"','"& email &"','"& nomeimpresa &"','"& cellulare &"');"

If this doesn't fix the problem, please add the following lines after line 24:

response.write(SQL)
response.end

This will show you the query, and hopefully you will see what is wrong.

Please note :

This way of mixing variables and code is a very big security issue called SQL injection. You can read more about SQL injection here. (take a look at the examples)

To prevent SQL injection in classic ASP, there are different methods. I suggest you try Google to find some. Here is an example.

I totally agree about sql injection but the article you referenced with classic asp is a joke. Even classic asp supports parameterized queries. Creating a "black list" of invalid words is horrific. It prevent actual data from being inserted and is isn't good enough anyway. — Sean Lange, Aug 28 '17 at 14:18

VBScript Registration [ASP] MSSQL]

1 Answers1