Volusion's generic SQL folder, functionality

Question

I found this response very helpful

What I'm looking for is a way to add my own .SQL and .XSD files to the /vspfiles/schema/Generic folder and be able to pass parameters to it. Does anyone know if that's possible.

A very basic example of the SQL would be something like this...

select * from Orders where order_id = "-ORDERID-"

...and I'd be able to pass in the "-ORDERID-" as a variable.

Or even better the SQL file would just be this "-SQL-" and I could pass in the entire SQL string myself. Thanks!

score 3 · Answer 1 · answered May 07 '15 at 14:37

Thanks user357034 for getting me here (I'd "up" your answer but I'm new and don't have any reputation). I wanted to post the code I used in case others run into this. And also get any feedback if you see anything that looks goofy here.

First, I created an ASP file like so

Dim  orderid
Dim status
orderid = Request.QueryString("orderid")
status = Request.QueryString("status")
sql =   " update Orders " & _
        " set OrderStatus = '" + status + "' " & _
        " where Orderid in (" + orderid + ") " ; 

set fs=Server.CreateObject("Scripting.FileSystemObject")
set f=fs.OpenTextFile(Server.MapPath("./MY_FILE.sql"),2,true)
f.WriteLine(sql)
f.Close
set f=Nothing
set fs=Nothing

I FTPed that up to the "generic" folder on Volusion.

Next, in PHP, I call this file, similar to this...

 $asp =  file("http://MY_SITE/v/vspfiles/schema/generic/MY_FILE.asp?
         orderid=11,12&status=Processing");
 foreach ( $asp as $line )
 {
     echo ($line);
 }

NOTE: I already FTPed an XSD file to the same folder with the same name, like MY_FILE.xsd.

And finally, I make a web service call to my service, like this...

   $url = "http://MY_SITE/net/WebService.aspx?
           Login=XXXX&EncryptedPassword=YYYYY&API_Name=Generic\MY_FILE"

Works great. I go into the Volusion admin site, look at the Orders 11 and 12, and they were updated. I'm using this method for several areas in Volusion where their API is lacking. Thanks!

While what you are doing will work, you may want to sanitize your incoming query string data to prevent possible SQL injection. So for instance check the status QS for known possible values and check the order id QS to make sure they contain numbers. Only then will it run the update query, otherwise one could easily insert there own query if they figure out your "MY_FILE" name. — user357034, Jul 25 '15 at 11:59

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

0

While technically one could pass in text comprised of a complete SQL query, however I would strongly caution against such practice as it would open up your site to malicious activity and/or possible security issues. I would limit the scope to a specific query and only allow one or more parameters to be used.

To accomplish this you have to create an custom ASP page in Volusion that would gather the parameters from the user, in your case "order id" and then insert them into the set SQL query, write that SQL query as a text file to the server in the correct location as shown in https://stackoverflow.com/a/29134928/357034 and then execute the query. All this is done in the custom ASP page.

UPDATE: If you just want a simple order id query with all fields returned you don't even have to use the the SQL method as Volusion already has a built in method to return this data. You just have to use a custom ASP page to insert the order id parameter and then execute the URL with the parameter attached. In your ASP page you would insert the order id in place of the xxxxx

http://www.yoursiteurl.com/net/WebService.aspx?Login=name@yoursiteurl.com&EncryptedPassword=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC&EDI_Name=Generic\Orders&SELECT_Columns=*&WHERE_Column=o.OrderID&WHERE_Value=xxxxx

edited May 23 '17 at 12:10

Community

1
1

answered Apr 26 '15 at 11:58

user357034

10,731
19
58
72

You cannot pass a custom SQL query or use multiple query parameters in the URL shown above. To get around this as I said previously you would need to build a custom ASP page that you would pass in the parameters from whatever application you are running, even multiple parameters. Then form the SQL query server side with those parameters and execute the query as mentioned above and in stackoverflow link that I provided. The Volusion API will respond with the data and return it to your application. – user357034 Apr 27 '15 at 21:55
Seems like a really clunky solution, but that's what I'm doing now (until Volusion support gets back to me -- which seems unlikely). I'm using PHP. I'm creating a SQL file on the fly, sFTP-ing it up to Volusion's "generic" folder, and then calling my file (to execute the SQL) through the Web Service call. It feels like a very heavy process (takes 3-5 seconds, mostly FTP time). I'd be much happier if I could send in parameters on the web service call to plug into my SQL....oh well. Thanks for the help! – theCodeWhisperer Apr 29 '15 at 13:41
Again, you can do exactly what you want to do you just need to create an ASP page on your Volusion site which retrieves the parameters sent to it and then writes the SQL query with these parameters to a text file in the "generic" folder. Once written and verified that the file exists this ASP page executes the text file's SQL query and returns the data requested. It's actually a pretty straight forward process. – user357034 Apr 29 '15 at 21:59
I don't know if I mentioned, I don't want to do this from a web page in my Volusion store. I want to do this from a different/remote system altogether. I want to insert/update products, retrieve/update orders, insert discounts, etc -- all from a remote system, not from with Volusion pages. That's why a Web Service call that can take dynamic parameters (or SQL) appeals to me more than FTPing the complete SQL file to generic folder and then calling it via WebService. – theCodeWhisperer Apr 30 '15 at 15:10
1

I fully understand what you what to do and I am telling you how to accomplish what you want. Your don't have to do constant FTPing as you have stated. You do have to create an Classic ASP page to act as an intermediary as you cannot do it directly and quite frankly for security reasons you wouldn't want that anyway. Now if you choose to ignore what I am saying, well I tried... – user357034 Apr 30 '15 at 22:27
I think I understand what you mean. I tried to upload an ASP file to my Volusion store's generic folder, but I'm not sure what the path is to access it...it doesn't work like this www.myStore.com/vspfiles/schema/Generic/test.asp. Do you know where I put the ASP file and how to access it? – theCodeWhisperer May 01 '15 at 14:37
Just figured it out, it's myStore.com/v/vspfiles/schema/generic/test.asp -- trying it now. Thanks again! – theCodeWhisperer May 01 '15 at 16:37

Volusion's generic SQL folder, functionality

2 Answers2

Linked