Developer forum

Forum » Development » Razor sql select less than og equal to error via querystring

Razor sql select less than og equal to error via querystring

Peter Klünder
Reply

Hi

I'm new to razor and trying to get top 1 of prices but nomatter what I do I get errors :)

Below code is just to show what I would like

code

var testvalue = @request["q"];    

var sql = "select top 1 * from EcomPrices where PriceQuantity <= '@testvalue' PriceProductID = 'PROD262' order by PriceQuantity desc";
var strprices = Database.CreateDataReader(sql);

code

Best Regards

Peter


Replies

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

Hi Peter

You have to be really careful here - this approach (using data from querystring in a SQL) is a major security risk and should be avoided. Or done differently.

You should use a commandBuilder to handle the security issues:

https://doc.dynamicweb.com/api/html/0634be7f-4d54-c680-02b6-ef5ddf7af346.htm

You can do something like this:

var testvalue = Dynamicweb.Core.Converter.ToDouble(System.Web.HttpContext.Current.Request["q"]);
var commandText = "select top 1 * from EcomPrices where PriceQuantity <= {0} PriceProductID = {1} order by PriceQuantity desc"
var commandBuilder = new CommandBuilder();
commandBuilder.Add(commandText, testvalue , 'PROD262');
using (var myDr = Database.CreateDataReader(commandBuilder))
            {
                while (myDr.Read())
                {
                    double price = Dynamicweb.Core.Converter.ToDouble(myDr["PriceAmount"]);
                }
            }

<div>The price: @price</div>

But this does not seem to be the right way to go. If you describe what you are trying to achieve (and in what template), we might be able to point you in the right direction.

I.e. you want to show the lowest quantity price for a given product in the product detail template?

BR Nicolai

 
Peter Klünder
Reply

Hi Nicolai

Security was on my mind :) but wanted to tackle that when I got it to work.

I'm trying to get the price for a given product when I via ajax submit a quantity.

example:

Product1

  • quantity 0-100 = 23kr
  • quantity 101-200 = 20kr
  • quantity 201-250 = 18kr
  • and so on.

The user then types in the quantity ex: 125, and updates and it fetches 20kr

It could be i'm doing it all wrong :)

/Peter

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply
This post has been marked as an answer

If you are in the product template, you have a prices loop: https://doc.dynamicweb.com/template-tags/ecommerce/product-catalog/product/product-prices

That should contain the data you need. Then inside that loop, look at the value of Ecom:Product.Prices.Quantity tag.

Votes for this answer: 1
 
Peter Klünder
Reply

Hi Nicolai

I'll go that way :) , thank you.

/Peter

 
Peter Klünder
Reply

Hi Nicolai

Perhaps you can guide me in the right direction, I would like to get the specific price for the quantity delivered via querystring, but can't seem to wrap my head around how to do it.

If the quantity amount changes it should fetch the equivalent price pr unit as I have typed ind product.prices.

Best regards

Peter

 

You must be logged in to post in the forum