Microsoft SQL Server Native Client 10.0 in asp page

Question

I am working in classic asp page, i got an error while executing the sql command, it's syntax error. I don't have knowledge in classic asp pages, so i request some one to sort it out..

Here is my code

exe = "INSERT INTO Item_table(Supplier_Profile_id,ImageType1,ImageType2,
Date_Made_Available,Date_last_edited,Approval_date,Approval_code,Item_number,On_hold,Duplicated_image,Order_ability,OmitFromSearch,View_order,PreviewAD,Item_type,Item_title,Item_dimensions,Item_price,Item_Price_Range,Search_Price_Point,Item_description,Date_image_uploaded,New_work_date,Product_code_General,Product_Code_Specific,Product_key_words,Product_media_code,MediaSpecificID,Product_style_code,Product_theme_code,Photo_credit,RootImage,ViewSearch,ViewSearch2,Best_Seller,MediaType,SortNum,BuyerHit,VisitorHit) VALUES("`

    exe = exe & "" & safeNumber(ThisSupplier_Profile_id) & ","
    exe = exe & "'" & Session("ImageType1") & "',"
    exe = exe & "'" & Session("ImageType2") & "',"
    exe = exe & "'" & now()& "',"
    exe = exe & "'" & now()& "',"
    exe = exe & "NULL,'N',"
    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Request("Item_number"))) & "',"
    exe = exe & "'" & Request("On_hold") & "',"     '--- On_hold
    exe = exe & "'',"
    exe = exe & "'" & Request("Order_ability") & "',"
    exe = exe & "'',"
    exe = exe & "'" & safeNumber(Request("View_order")) & "',"
    exe = exe & "'',"
    exe = exe & "'" & safeNumber(Request("Item_type")) & "',"
    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Request("Item_title"))) & "',"
    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Request("Item_dimensions"))) & "',"
    exe = exe & "'',"
    exe = exe & "'',"
    exe = exe & "'" & safeNumber(Request("Search_Price_Point")) & "',"
    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Request("Item_description"))) & "',"
    exe = exe & "'" & now() & "',"                                      
    exe = exe & "" & Session("insertNew_work_date") & ","               
    exe = exe & "'" & safeNumber(Product_code_General) & "',"
    exe = exe & "'" & safeNumber(Product_Code_Specific) & "',"

    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Product_key_words)) & "',"
    exe = exe & "'" & MediaTypeID & "',"        '--- Product_media_code   
    exe = exe & "'" & MediaSpecificID & "',"

    exe = exe & "'" & safeNumber(Request("Product_style_code")) & "',"

    exe = exe & "'" & safeNumber(Request("Product_theme_code")) & "',"

    exe = exe & "'" & HandleDoubleQuotes(safeEntry(Request("Photo_credit"))) & "',"
    exe = exe & "'',"           '--- RootImage
    exe = exe & "'" & safeEntry(Request("ViewSearch1")) & "',"
    exe = exe & "'" & safeEntry(Request("ViewSearch2")) & "',"
    exe = exe & "'" & safeEntry(Request("Best_Seller")) & "',"
    exe = exe & "'" & safeEntry(Request("Media")) & "',"
    exe = exe & "0,"
    exe = exe & "0,"                    '--- BuyerHit
    exe = exe & "0); select @@identity" '--- VisitorHit

Set RS1 = Conn.Execute(exe).nextrecordset

ERROR: Incorect syntax near ","

Please format your question properly. There is a `{}` button the editor for code. — juergen d, Jul 15 '14 at 11:54
Questions here need to be self-containt. Please don't post relevant info in links. If the link breaks your post is useless. And we want questions be useful for future visitors too — juergen d, Jul 15 '14 at 11:57
Yes it is. Concatenating a query like you do it can be a pain as you just experience it. Try changing your query into a *Prepared Statement*. This will save you a lot of other trouble too. — juergen d, Jul 15 '14 at 12:05
A downvote implies that there is something wrong with a post. Good and interesting questions get upvotes. Not so good ones downvotes. — juergen d, Jul 15 '14 at 12:06
Do a `response.write exe` and a `response.end` before you do the `.execute` and see the actual sql being executed; you could paste it here too. — Flakes, Jul 17 '14 at 06:00

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

First, if you tend to take seriously with stackoverflow.com, I suggest you should read through the stackoverflow tour

Then, for your question, the error should come from SQL server, not asp. Your code just collect all variable, add to the string "exe" and send that string to SQL server. Therefore, there are two places that possibly made error:

1) exe = exe & "'',"

It will pass in a ' two single quotes ' to SQL server

2) Your method: HandleDoubleQuotes()

Check this method, does the return value is a string with blacklist characters?

Finally, I suggest you use Parameters to pass the data in. It should be safer for injection, as well as handling all syntax error.

EDIT: Point 1 is a valid, as corrected by Paul.

edited May 23 '17 at 11:45

Community

1
1

answered Jul 15 '14 at 12:40

Jacky

2,924
3
22
34

Point `1` is valid (though potentially bad form) SQL. It's perfectly valid to put an empty string into any form of character based field. I say potentially bad form, as it's possible that (if the string were to be analysed later) an empty string may have a specific connotation than `NULL`. – Paul Jul 15 '14 at 12:46
@Paul ,Thanks, I have went through his code again to double check, and yes, point `1` is valid. Edited my answer. – Jacky Jul 15 '14 at 13:30

score 0 · Answer 2 · edited May 23 '17 at 12:08

Though not a complete answer, there are a number of things you can do with this.

Firstly, if you don't follow Juergen's advice and put the code into something like a stored procedure, then you could massively simplify your code in a number of ways.

Firstly, instead of having reams and reams of concatenation, try using the line extender character for VB Script. Also, break your lines so it's actually legible, like so:

exe = "INSERT INTO Item_table(Supplier_Profile_id,ImageType1,ImageType2," & _
    "Date_Made_Available,Date_last_edited,Approval_date,Approval_code,Item_number," & _
    "On_hold,Duplicated_image,Order_ability,OmitFromSearch,View_order," & _
    "PreviewAD,Item_type,Item_title,Item_dimensions,Item_price," & _
    "Item_Price_Range,Search_Price_Point, Item_description,Date_image_uploaded," & _
    "New_work_date,Product_code_General, Product_Code_Specific,Product_key_words," & _
    "Product_media_code,MediaSpecificID, Product_style_code,Product_theme_code," & _
    "Photo_credit,RootImage,ViewSearch, ViewSearch2,Best_Seller,MediaType,SortNum," & _
    "BuyerHit,VisitorHit) VALUES(" & _
    safeNumber(ThisSupplier_Profile_id) & "," & _
    "'" & Session("ImageType1") & "'," & _
    "'" & Session("ImageType2") & "'," & _
    "'" & now()& "'," & _
    "'" & now()& "'," & _
    "NULL,'N'," & _
    "'" & HandleDoubleQuotes(safeEntry(Request("Item_number"))) & "'," & _
    "'" & Request("On_hold") & "'," & _
    "''," & _
    "'" & Request("Order_ability") & "'," & _
    "''," & _
    "'" & safeNumber(Request("View_order")) & "'," & _
    "''," & _
    "'" & safeNumber(Request("Item_type")) & "'," & _
    "'" & HandleDoubleQuotes(safeEntry(Request("Item_title"))) & "'," & _
    "'" & HandleDoubleQuotes(safeEntry(Request("Item_dimensions"))) & "'," & _
    "''," & _
    "''," & _
    "'" & safeNumber(Request("Search_Price_Point")) & "'," & _
    "'" & HandleDoubleQuotes(safeEntry(Request("Item_description"))) & "'," & _
    "'" & now() & "'," & _
    Session("insertNew_work_date") & "," & _
    "'" & safeNumber(Product_code_General) & "'," & _
    "'" & safeNumber(Product_Code_Specific) & "'," & _
    "'" & HandleDoubleQuotes(safeEntry(Product_key_words)) & "'," & _
    "'" & MediaTypeID & "'," & _
    "'" & MediaSpecificID & "'," & _
    "'" & safeNumber(Request("Product_style_code")) & "'," & _
    "'" & safeNumber(Request("Product_theme_code")) & "'," & _
    "'" & HandleDoubleQuotes(safeEntry(Request("Photo_credit"))) & "'," & _
    "''," & _
    "'" & safeEntry(Request("ViewSearch1")) & "'," & _
    "'" & safeEntry(Request("ViewSearch2")) & "'," & _
    "'" & safeEntry(Request("Best_Seller")) & "'," & _
    "'" & safeEntry(Request("Media")) & "'," & _
    "0," & _
    "0," & _
    "0); SELECT @@identity"

Secondly, it's better to use parameters in Classic ASP (with any programming language any language, really).

Thirdly, based on what you've pasted into your post above, I would suspect that you have a line break in your "INSERT INTO Item_table(Supplier_Profile_id, ... line, just before Date_Made_Available, .... That would explain the blank lines in your post above.

Microsoft SQL Server Native Client 10.0 in asp page

2 Answers2