|
| |
|
|
cfreema7
Posts: 41
Joined: 6/4/2003
Status: offline
 |
Ampersand in name causes error in INTO statement - 9/7/2003 19:44:19
Another issue in the same vein: The information gets entered into a purchase_order database, and now I'm getting a:
"[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. "
I notice this happens on the cards where the card has "&" in the title, like "Hotel & Casino".
This is the SQL statement:
SQLQuery = "insert into ShopCart " & _
"(ProductName, UnitPrice, ShippingCost, Message, SenderName, SenderEmail, RecName, RecAddress1, RecAddress2, RecCity, RecState, RecZip, RecCountry, SessionID, ProductID, Qty, EntryDate) values " & _
"('"& Session("ProductName") & "', " & session("ProductUnitPrice") & "," & session("ShippingCost")& ", '" & replace(session("message"),"'","''") & "', '" & session("SenderName") & "', '" & session("SenderEmail") & "','" & session("RecName") & "','" & session("RecAddress1") & "','" & session("RecAddress2") & "','" & session("RecCity") & "','" & session("RecState") & "','" & session("RecZip") & "','" & session("RecCountry") & "','" & uniqueID &"'," & Session("ProductID") & ", 1, '" & cstr(date()) & "')"
ProductName is the title that contains the "&" and is causing the error, but I don't know how to isolate it. Is it possible to do this, or should I just remove the "&"?
Thanks again
_____________________________
Chris Freeman
www.postcardsfromthenet.com
www.evaguru.com
www.sflocals.com
|
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/7/2003 23:14:51
One method:
Writing to DB:
<%
variable=replace(request("whatever"),"&","~")
%>
Displaying:
<%
variable=replace(RS("whatever"),"~","&")
%>
This is viable if this input data info is a known, on-going source of this problem. I say this, because "Replace" is known to cause memory leak in earlier IIS versions.
Of course, you could always test for the condition first:
<%
if inStr(request("whatever"),"&") then
variable=replace(request("whatever"),"&","~")
end if
%>
Displaying:
<%
variable=replace(RS("whatever"),"~","&")
%>
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/8/2003 9:44:03
quote:
Could you write the SQLQuery value to confirm how the finished string looks?
Response.write SQLQuery
Response.end
Unless he plans on visually filtering the data before adding it to the DB, I can't see how that would be of much use.
Hence, the best strategy for making sure that your data doesn't have a character that will cause problems is to make sure that such things don't get in there in the first place, i.e:
Java Script validation
Example:
I've got a select box named: "tpep" which sets what an input text box is to update.
Option 2 tells my code that the "replp" text box means that the user is entering a value for "quantity".
Needless to say, quantity MUST be a numeric value.
Enter Java Script:
var checkOK = "0123456789.";
var checkStr = editpro.replp.value;
var allValid = true;
for (i = 0; i < checkStr.length; i++)
{
ch = checkStr.charAt(i);
for (j = 0; j < checkOK.length; j++)
if (ch == checkOK.charAt(j))
break;
if (j == checkOK.length)
{
allValid = false;
break;
}
}
if ((editpro.tpep.value == 2)&&(!allValid))
{
alert("Please enter numeric characters only when changing the \"Quantity\".");
editpro.replp.focus();
return (false);
}
If bad data doesn't get in in the first place, it cannot cause you grief.
| |
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/8/2003 14:11:34
There isn't any inherent problem with using & in a text value for a column. I'm sure Spooky is thinking like I am, there is some other syntax error in the code or data provided to the code, which usually becomes quite obvious when you response.write the final sql string out to your browser and look at it.
_____________________________
======
Doug G
======
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/8/2003 15:33:25
quote:
There isn't any inherent problem with using & in a text value for a column. I'm sure Spooky is thinking like I am, there is some other syntax error in the code or data provided to the code, which usually becomes quite obvious when you response.write the final sql string out to your browser and look at it.
Actually, there is an inherent problem, as use of an Ampersand renders the data useless for use in querystring attachments, and here's a real-life example:
I wrote a delete routine that wrote out the ID value of a product value like so:
<%
response.write("<td bgcolor=#ffffff height=24 width=14.5%" & "><a href=" & chr(34) & "deleteitem.asp?delp=" & RS("value") & chr(34) & " onClick=" & chr(34) & "confirmSubmit=confirm('Delete Product-cannot be restored!'); return false" & chr(34) & "><img src=" & chr(34) & "images/delprod.gif" & chr(34) & " border=0 alt=" & chr(34) & "Delete Product-cannot be restored" & chr(34) & "></a></td></tr></table>")
%>
When the image is clicked, it sends RS("value") to "deleteitem.asp" where I use a "Select * WHERE value = '"&RS("value")"'"
Now, this worked great for the first 360 or so clients, then some yahoo used a product ID that looked like this:
31648&31
The DB could never find "31648&31", because the value presented to "deleteitem.asp", could never be found, and an error was generated. What it found was:
316448
Why? Because use of the ampersand signals the end of your value, and beginning of the next.
I solved it by testing for an ampersand, and replacinging it with another character. Then, before writing to the DB, I test for the new character, and reverse the procedure.
The moral? Like a boyscout, you've gotta be preparred!
You may take a performance hit, but you've gotta use:
trim(value)..to remove spaces(it's a good idea in many cases)
and test for these characters : ' & % > $(in a currency field)
< Message edited by thecharacterguy -- 9/8/2003 3:35:32 PM >
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/8/2003 16:02:41
Write will always reveal the problem, but who writes it out and looks at it before entering it?
That's the purpose of having code...to automate the procedure so you don't have to have a person enter it.
Hence, JS is the ultimate answer. If it doesn't get in in the first place, it can't cause problems.
| |
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/8/2003 19:25:21
quote:
Actually, there is an inherent problem, as use of an Ampersand renders the data useless for use in querystring attachments, and here's a real-life example:
Not to beat a dead horse, this is not relevant to an sql string. There is not a problem using & in the text for a textual column in sql.
_____________________________
======
Doug G
======
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/9/2003 11:21:03
quote:
99 out of 100 people will tell you to response.write the string first.
Gotta disrespectfully disagree.
Couple of things here.
The writer tells us that his code "works" most of the time. So it's an obvious conclusion that if you know enough to create the same error as the user, you don't need to write it out....you already know enough to fix the error!
Secondly, the author provides us with the answer himself in his own code:
"('"& Session("ProductName") & "', " & session("ProductUnitPrice") & "," & session("ShippingCost")& ", '" & replace(session("message"),"'","''") & "', '" & session("SenderName") & "', '" & session("SenderEmail") & "','" & session("RecName") & "','" & session("RecAddress1") & "','" & session("RecAddress2") & "','" & session("RecCity") & "','" & session("RecState") & "','" & session("RecZip") & "','" & session("RecCountry") & "','" & uniqueID &"'," & Session("ProductID") & ", 1, '" & cstr(date()) & "')"
No need to write it...it's obvious. Here's what he'll get:
'" & Hotel & Casino & "',
The use of the ampersand creates a syntax error, as the server attempts to record only "Hotel", and looks for the end, i.e:
"',
you see Spooky, "the little man in the computer" doesn't know that the ampersand is to link Hotel with Casino. He thinks that you're asking him to perform a task.
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/9/2003 20:01:57
quote:
No, thats not how it works.
If you have a variable "Hotel & casino", when thats inserted into the SQL string, it will remain "Hotel & casino"
('"&value&"')
Value gets replaced, "&" isnt even counted as part of the asp string.
Dont get confused with the use of single quotes, which will escape the SQL string
Look again:
replace(session("message"),"'","''")
He's altering the value in the string, so it isn't a literal.
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/9/2003 20:19:49
quote:
This code snip will "escape" single apostrophe's in an existing string to double apostrophes, which is needed by MS and other databases to allow a single apostrophe to be contained in a text string value. This is necessary to prevent sql injection attacks, among other things, and if it were me I'd do this replace on all the text columns just in case
Exactly! BUT- you do this before the insert.
(I have a different technique. I replace it with chr(180), which is an acute apostrophe)
Anywhoooo, the insertion of the replace routine renders the value to be not literal, hence, the ampersand causes a syntax error.
| |
|
|
|
thecharacterguy
Posts: 15
Joined: 9/6/2003
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/10/2003 9:06:06
quote:
The error is 'supposedly' coming from a different field (productname not Message) , hence the need to write the SQL string.
I would suggest that the use of 'session' is part of the problem
One more time:
The author writes:
I notice this happens on the cards where the card has "&" in the title, like "Hotel & Casino".
Since the developer can control the value of a fixed variable, the fact that an ampersand is getting in implies that it's coming from an area that the user is inputting, namely, a text area or input text box.
"productid" will quite obviously be: card
If productid is not "card", and contains title information, then the developer was using an input text box or text area and allowing the users of his software to define the value of productid, which makes no sense.
Hence, "message" is what he refers to, and it makes sense. A "message" has a title, not a productid.
As I originally noted, the ampersand is thus "not literal", and is interpreted as a concantenation operator, and throws a monkey wrench into the syntax.
Lastly, I'd wager the author is laughing too,
I know I am!
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/10/2003 10:36:47
Seems to me that only Chris knows for sure what is going on here, however, at the end of his post he says this:
quote:
ProductName is the title that contains the "&" and is causing the error, but I don't know how to isolate it. Is it possible to do this, or should I just remove the "&"?
When he refers to "cards" he is talking about postcards (that is what he sells on his site). The ProductName is a field he controls. He is the one that named all of the products in his db. That is why he is asking whether he should just delete the "&" because evidently some of the product names he chose contain a "&". I am guessing that he pulls information from a products table and when the customer places an order some of that information follows into the ShoppingCart table.
If he happens to come back and read this, I would want to know why he wouldn't just insert the productID into the shoppingcart instead of also including the ProductName, but nonetheless that is what he is doing.
Sooooo, his issue appears to be why is he getting an error when the Product name field contains a "&" and can he fix it or is he better off deleting the "&" out of the product name field.
quote:
"productid" will quite obviously be: card
CharacterGuy, are you sure about the above statement? The SQL seems to indicate that productid is a numeric field, and "card" is obviously not numeric. I am fairly certain that productid is the id of the postcard from another table that contains his postcard inventory.
Judging from his site, Message is a memo field and while it is possible that an ampersand in that field could cause him a problem, he clearly identifies the ProductName field as the culprit in this particular instance.
Of course, much of this conversation may have been avoided if we could see the finished string as Spooky asked earlier. Since Chris hasn't interjected, I too wonder if he already fixed it. If so, Chris, please tell us and put an end to this madness.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Ampersand in name causes error in INTO statement - 9/12/2003 10:39:33
quote:
amanuensis
Can't say I knew it either, but I think my daughter had to spell it once in a school spelling bee. Think she got it right, too. One of those proud father moments.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
