May 23, 2012 at 3:19 pm
I have a .asp form that uses text and textarea for input. I use SP's to update MS SQL 2005 tables. My problem is that when a user enters text surrounded by quotation marks ("text"), the UPDATE truncates the field beginning just before the first quotation mark.
I use Replace(Request.Form("fieldname"),"'", "''") to deal with this, but it's not working. (These are apostophe's between quotation marks.)
Suggestions?
Thanks in advance.
May 24, 2012 at 1:45 am
In the SP, are you constructing the SQL dynamically and then executing it? Also, are you constructing the procedure call from your asp page dynamically as well? as if you are then it sounds like from your post that it could also be open to SQL injection type attacks. Parameters should be used to pass the information into the procedure.
I found this similar post on stackoverflow which gives you example code on how to call procs with parameters. You should be able to use this to pass through the contents of your textarea without any replacing of characters.
http://stackoverflow.com/questions/1974692/passing-variables-to-stored-procedure-with-classic-asp
If you still get a bit stuck then post some of your relevant code from the ASP page and proc so that we can help further.
May 24, 2012 at 9:23 am
Thanks for your suggestion, but I really don't understand your link's contents.
Here's some additional info:
1. textarea field named NOTES input example: steve's notes go "here"
2. .asp page that updates the SQL table has these lines
--- notes=Replace(Request.form("notes"),"'", "''")
--- strSQL="uspSVdPSVolNotes '"& notes &"', '" & seqid & "' "
--- cn.Execute(strSQL)
3. here's the output: uspSVdPSVolNotes 'steve''s notes go ', '313'
As you can see, the input is truncated at the point of the first quotation mark.
What am I doing wrong? Thank you.
May 24, 2012 at 9:39 am
What we would need to do is to slightly change the way that the stored procedure is being called from the asp page. You've not posted how the connection to the database is created, but I can see that it is being referenced by the object named cn. So I have adapted the code in the post on stackoverflow to match roughly what yours should ideally look like. Bear in mind that I have no easy way of testing this before posting it to you.
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "uspSVdPSVolNotes"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1) = Request.form("notes")
cmd.Parameters(2) = seqid
cmd.Execute
this link from microsoft may help you as well http://support.microsoft.com/kb/164485
May 24, 2012 at 12:38 pm
Have done what you suggest, still have the issue. It looks like my problem is occurring BEFORE the database update. Here's my code, slightly edited from yours, to include output of the contents of the field named NOTES:
cmd.CommandText = "uspSVdPSVolNotes"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1) = Request.form("notes")
cmd.Parameters(2) = seqid
'cmd.Execute
Response.Write Request.form("notes")
Here's the results of the Response.Write:
steve's notes go
As you can see, the field is truncated just before the first ". Any ideas? Thank you.
May 24, 2012 at 1:15 pm
Ok that looks like to me that the contents of that field is changing before getting to that point in the code. Im fairly confident From what you have posted that it is not a sql issue however I'm not an asp expert by any means. I think we are going to see more of the code on the asp page to diagnose the issue further. Can you post some more? Or private message me with the asp page in question?
May 30, 2012 at 10:56 am
The asp code is pretty simple. I have three .asp files involved: the form itself, a confirmation page and a file that updates the database, then redirects the user to another page.
The confirmation page contains <textarea cols="40" rows="5" readonly><%=Request.form("notes")%></textarea> to properly show the double quotes within a text area. This page also contains a hidden field to pass along the value to the update page, as follows: <input type="hidden" name="notes" value="<%=Request.form("notes")%>">
The update logic page uses a CASE to get the UPDATETYPE from the confirmation page. Here's it's update code:
case "editnotes"
cmd.CommandText = "uspSVdPSVolNotes"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1) = Request.form("notes")
cmd.Parameters(2) = seqid
cmd.Execute
The sequid is a client id and is obtained using Request.form("seqid") up the page. It is in this update step where the value of the "notes" field is truncated exactly where the double quotes appear.
Thank you.
May 30, 2012 at 11:42 am
steveg-941384 (5/30/2012)
The asp code is pretty simple.The confirmation page contains <textarea cols="40" rows="5" readonly><%=Request.form("notes")%></textarea> to properly show the double quotes within a text area. This page also contains a hidden field to pass along the value to the update page, as follows: <input type="hidden" name="notes" value="<%=Request.form("notes")%>">
Thank you.
Not sure why you are using a textarea and a hiddenfield to hold the same data but your issue is in your hidden field. You did not escape your double quotes when you write this out again. Notice where you write value="<%=Request.form("notes")%>"
Because of the nature of response.write you are ending your value property. Load your confirmation and look at the source. It will have value="your data up until the first double quote" then the rest of your textbox just tossed inside the hiddenfield tag./> or something like that.
To fix this you need to do a replace on your request.form value and replace " with "".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 12:05 pm
Thank you for your suggestion.
Here's what I've done to the update page:
cmd.Parameters(1) = Replace(Request.form("notes"),""", """")
but it results in an asp error:
Microsoft VBScript compilation error '800a0409'
Unterminated string constant
/svdps_update.asp, line 162
cmd.Parameters(1) = Replace(Request.form("notes"),""","""")
-----------------------------------------------------------^
I get the same error using the Rplace on the confirmation page as well. Where have I misunderstood you?
May 30, 2012 at 12:31 pm
<input type="hidden" name="notes" value="<%=Replace(Request.form("notes"), "", """")%>">
I think that should be close. You don't want to do that in your parameter or it will replace in the value which is not what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 1:48 pm
here's the html source after updating per your suggestion:
<input type="hidden" name="notes" value="steve's notes go "here"">
Same truncation problem. Any other ideas?
Thank you!
May 30, 2012 at 1:52 pm
steveg-941384 (5/30/2012)
here's the html source after updating per your suggestion:<input type="hidden" name="notes" value="steve's notes go "here"">
Same truncation problem. Any other ideas?
Thank you!
Can you post the code that you used for this field? It is obvious from the view source output that is your problem. The value in your input is
steve;s notes go
. the next piece
here""
is just invalid html that most browsers will just ignore. It is in populating this hidden box where your problem lies.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 2:01 pm
<input type="hidden" name="notes" value="<%=Replace(Request.form("notes"), "", """")%>">
Here's the text entered into the "notes" field: steve's notes go "here"
Is the single quote in steve's causing the issue?
Thank you.
May 30, 2012 at 2:25 pm
Oh geez...it has been awhile since I dealt with this stuff. :blush:
You have to encode your data prior to using it inside an attribute.
Try this.
<input type="text" name="MyVal" value="<%=server.HTMLEncode(request.form("notes"))%>" />
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 3:16 pm
That worked. I can't thank you enough!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply