CODEWITHSUNDEEP
jqueryjsonasp-classic
Jason Smith
Jason Smith

Reputation: 11

ASP & jQuery UI: autocomplete with json source

I am trying to get a autocomplete textbox using classic ASP and Jquery below is the code i currently have but nothing is populating in the search box.

Database Name = Test

Database User Login = sql

Password = Password

Table Name = Product

Columns are: ProductId, Name, ItemNumber

I am trying to allow the user to search by product name. Also do I have a risk of SQL injection with the following code? Thanks in advance!

search.asp:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2  /jquery.js" ></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.js" ></script>
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/themes/base/jquery-ui.css"/>

<!-- SCRIPT FOR AUTOCOMPETE SEARCH BOX //-->
<script type="text/javascript" language="javascript">
<!--
    $(function() {
        $( "#productname" ).autocomplete({
        source: "source.asp",
        minLength: 2
    });
   });
// -->
</script>

</head>
<body>


<p>&nbsp;</p>

<div>
<input type="text" id="productname">
</div>

<p>&nbsp;</p>

</body>
</html>

and my source page is:

<%
Dim keywords
Dim keywords_cmd
Dim output 


Set keywords_cmd = Server.CreateObject ("ADODB.Command")
keywords_cmd.ActiveConnection = "Provider=SQLNCLI10;Server=LOCALHOST\SQL;  Database=test;Uid=sql; Pwd=Password;" 
keywords_cmd.CommandText = "SELECT ProductId, Name FROM product where Name like '%"  & Request.QueryString("term") & "%'"
keywords_cmd.Prepared = true

Set keywords = keywords_cmd.Execute

output = "["

While (NOT keywords.EOF)
output = output & "{""ProductId"":""" & keywords.Fields.item("ProductId") & """,""value"":""" & keywords.Fields.Item("Name") & """},"
 keywords.MoveNext()
While end

keywords.Close()
Set keywords = Nothing

output=Left(output,Len(output)-1)
output = output & "]"
response.write output

%>

Upvotes: 1

Views: 2404

Answers (2)

Sturb
Sturb

Reputation: 81

I had the same problem and fixed it by adding in the Response.ContentType row shown below.

... output = output & "]"

Response.ContentType = "application/json"

response.write output

Upvotes: 0

Fu-Raz
Fu-Raz

Reputation: 73

First off, "yes". This code is open to SQL injection. This is because you're using the QueryString parameter directly into your SQL Query:

"SELECT ProductId, Name FROM product where Name like '%"  & Request.QueryString("term") & "%'"

What you should do is use parameters.

Also, I believe jQueryUI autocomplete needs a label property. And your script doesn't automatically return the content-type: application/json.

This is what I made of it:

Response.CodePage = 65001
Response.CharSet = "UTF-8"
Response.ContentType = "application/json"

Dim keywords, keywords_cmd, output, firstItem

Set keywords_cmd = Server.CreateObject ("ADODB.Command")
With keywords_cmd
    .CommandType = adCmdText
    .ActiveConnection = "Provider=SQLNCLI10;Server=LOCALHOST\SQL;  Database=test;Uid=sql; Pwd=Password;" 
    .CommandText = "SELECT ProductId, Name FROM product where Name like ?"
    .Parameters.Append .CreateParamter("@term", adVarchar, adParamInput, 200, "%" & Request.QueryString("term") & "%")
    Set keywords = .Execute
End With

output = "["
firstItem = True
While (NOT keywords.EOF)
    If Not firstItem Then output = output & ","
    output = output & _
                "{""ProductId"":""" & keywords.Fields.item("ProductId") & """," & _
                """value"":""" & keywords.Fields.Item("ProductId") & """," & _
                """label"":""" & keywords.Fields.Item("Name") & """}"
    keywords.MoveNext()
    firstItem = False
While end
output = output & "]"

keywords.Close()
Set keywords = Nothing
response.write output

Upvotes: 1

Related Questions