Ampersand in name causes error in INTO statement (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

cfreema7 -> 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



thecharacterguy -> 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"),"~","&")
%>



Spooky -> RE: Ampersand in name causes error in INTO statement (9/7/2003 23:49:35)

Could you write the SQLQuery value to confirm how the finished string looks?

Response.write SQLQuery
Response.end



thecharacterguy -> 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 -> 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.



thecharacterguy -> 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)



Spooky -> RE: Ampersand in name causes error in INTO statement (9/8/2003 15:42:30)

Thats a reason to URLencode your output, but doesnt change the input problem.
Response.write usually reveals the problem ;-)



thecharacterguy -> 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.



Spooky -> RE: Ampersand in name causes error in INTO statement (9/8/2003 16:09:00)

We need to know what the problem is first, we are only making an assumption that its "&"
The error is fairly generic "Syntax error in INSERT INTO statement" so you need to ascertain what the issue is as many things can cause that.

99 out of 100 people will tell you to response.write the string first.



Doug G -> 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.



Spooky -> RE: Ampersand in name causes error in INTO statement (9/8/2003 19:35:43)

I wanna hear from Chris ;-)
Animal welfare will soon get a call otherwise [:)]



thecharacterguy -> 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.



Spooky -> RE: Ampersand in name causes error in INTO statement (9/9/2003 15:53:52)

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



thecharacterguy -> 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.



Doug G -> RE: Ampersand in name causes error in INTO statement (9/9/2003 20:11:57)

quote:

replace(session("message"),"'","''")

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 [:)]



thecharacterguy -> 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.



Spooky -> RE: Ampersand in name causes error in INTO statement (9/9/2003 20:39:08)

Not to be pedantic, but I will :-)

quote:

ProductName is the title that contains the "&" and is causing the error,


quote:

Look again:
replace(session("message"),"'","''")


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



BeTheBall -> RE: Ampersand in name causes error in INTO statement (9/9/2003 20:57:54)

Bet Chris thought this was just a simple question. [;)]



Spooky -> RE: Ampersand in name causes error in INTO statement (9/9/2003 21:03:35)

He probably fixed it ages ago without our help. Ill bet he's laughing his head off now [;)][sm=lol.gif]



thecharacterguy -> 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 -> 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.



Doug G -> RE: Ampersand in name causes error in INTO statement (9/10/2003 12:47:31)

quote:

Not to be pedantic, but I will :-)

[:)]



Spooky -> RE: Ampersand in name causes error in INTO statement (9/10/2003 15:50:34)

Doug [sm=lol.gif]

TCG, heres an example, perhaps you can explain what we are all not seeing. 

<%@ Language = "VBScript" %>
<%
Session("test") = "test & test"

sDSN = Application("Database1_ConnectionString")
sSQL = "Insert into Table (test_field) values ('"&Session("test")&"')"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open sDSN
objConn.Execute(sSQL),,adexecutenorecords
objConn.close : set objConn = Nothing
%>


Here we use a simple database insert to add a session value into a table.
It works correctly with no errors. Using your logic, where is the problem with this?



BeTheBall -> RE: Ampersand in name causes error in INTO statement (9/11/2003 17:06:34)

The people I work with have issued a formal request for me to stop referring to them as pedantic or calling them pedants. Always thought myself quite the wordsmith, but I guess this is a full service forum, coding and vocabulary all under one roof![sm=lol.gif]



Doug G -> RE: Ampersand in name causes error in INTO statement (9/11/2003 19:47:01)

quote:

The people I work with have issued a formal request for me to stop referring to them as pedantic or calling them pedants. Always thought myself quite the wordsmith, but I guess this is a full service forum, coding and vocabulary all under one roof!

Call out the amanuensis to record this for posterity [:)]



Spooky -> RE: Ampersand in name causes error in INTO statement (9/11/2003 20:27:29)

I admit it. I had to look the meaning of that one up [:)]



Doug G -> RE: Ampersand in name causes error in INTO statement (9/12/2003 0:12:57)

My favorite fat fictional detective (Nero Wolfe) taught me that word. Well, more accurately Archie Goodwin his irrepressible sidekick did. Rex Stout's novels were usually good for one or two new words per book [:)] And he wrote 50 some books (all of which are fun to read).



BeTheBall -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125