SQL String via form in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SQL String via form in ASP

Author

Topic

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 17:01:57

Hello!

I am passing a dynamically generated SQL string from a form to an update page via ASP.

Essentially, there is a hidden field on the form that builds the SQL statement, which I am processing on the 2nd page.

There is a syntax problem, I believe, with the way I am passing the quotes (single and double) and the ampersands. If someone wouldn't mind looking at the following code and letting me know what I am doing wrong, I would appreciate it: (Disregard the repeating row stuff - that all works just fine.


<input name="formsql" type="hidden" value=""UPDATE <%=Request.QueryString("inTable")%> SET <% While ((Repeat2__numRows <> 0) AND (NOT Recordset3.EOF)) %><%=(Recordset3.Fields.Item("Field").Value)%>='" & s<%=(Recordset3.Fields.Item("Field").Value)%> & "',<% Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
Recordset3.MoveNext()
Wend %> WHERE <%=Request.QueryString("inTable")%>ID=" & s<%=Request.QueryString("inTable")%>ID "/>

I get the following error, in which the returned SQL string looks right, which is what makes me believe the amps and quotes are not registering properly. If I manually insert the string, it works, just not off the form above:

"UPDATE news SET Title='" & sTitle & "',DateStamp='" & sDateStamp & "',LinkTitle='" & sLinkTitle & "',LinkURL='" & sLinkURL & "',ThumbImage='" & sThumbImage & "',LargeImage='" & sLargeImage & "',Body='" & sBody & "',AlternateText='" & sAlternateText & "',CategoryID='" & sCategoryID & "' WHERE newsID=" & snewsID

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '&'.

Thank you!
Keith

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 18:10:28

Have you tried running SQL Profiler to see what the ASP page is sending to the database?

btw I hope this isn't for a public website. Have you heard of SQL injection attacks?

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 18:14:41

quote:
Originally posted by timmy

Have you tried running SQL Profiler to see what the ASP page is sending to the database?

btw I hope this isn't for a public website. Have you heard of SQL injection attacks?

Hi thank you for your post. No on the SQL Profiler - not even sure how to run it...

This is actually on a password protected page so it's safe from injection. Thanks for the heads up though...

Thanks,
Keith

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 18:22:19

Keith,

Check out BOL on Profiler. It's really handy when you're not sure what your app is sending to the database.

And nothing is safe from SQL injection. Password-protection might limit the risk, but there's nothing stopping an authenticated user from hacking your site if they have the right knowledge. There's also the possiblity of passwords being compromised. SQL injection can be effectively eliminated by placing all your data access in stored procedures.

HTH,

Tim

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 18:29:14

Tim,

SQL Profiler wont run (it's on a shared server and I cannot get SysAdmin rights) so I will have to figure it out another way.

Thanks for your posts!

Keith

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 18:45:06

I've just read your original post again.

There is no way that

UPDATE news SET Title='" & sTitle & "',DateStamp='" & sDateStamp & "',LinkTitle='" & sLinkTitle & "',LinkURL='" & sLinkURL & "',ThumbImage='" & sThumbImage & "',LargeImage='" & sLargeImage & "',Body='" & sBody & "',AlternateText='" & sAlternateText & "',CategoryID='" & sCategoryID & "' WHERE newsID=" & snewsID

is a valid SQL statement. You need to ensure that all your values are subbing in correctly before you can execute the statement.
I would try putting a Response.Write on the form field in question on the destination page and making sure that is valid before attempting to hit the database.

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 18:52:36

Hi Tim,

It does work if I manually execute it - without any problems. It's a syntax issue with the form field, but not sure what....

Thanks,
Keith

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 19:02:46

I should clarify - It may be a valid SQL string, but I'm sure it isn't inserting what you want.

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 19:04:52

quote:
Originally posted by timmy

I should clarify - It may be a valid SQL string, but I'm sure it isn't inserting what you want.

It is :)

I just left out the rest of the code because I don't want to put the whole thing up here. The s fields hold the data passed from the form.

It works, really :)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 19:10:51

Then the best thing I could suggest is start from scratch with your UPDATE statement (ie. only update one field) and, after you're sute that the DB update works without error, start adding more fields.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-29 : 20:07:08

It is really so much easy to use parameters than it is to concatenate strings together ....

keifakeifa
Starting Member

6 Posts

Posted - 2006-06-29 : 22:30:52

quote:
Originally posted by jsmith8858

It is really so much easy to use parameters than it is to concatenate strings together ....

True, but for the sake of curiosity, would anyone be able to demonstrate a way to do it within the way I originally posted?

:)

Thanks for all of the posts!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources