asp:QueryStringParameter and empty query string parameter

Question

I haveasp:GridView displaying client requests using asp:SqlDataSource. I want to limit displayed information by client:

View.aspx has to display everything, View.aspx?client=1 has to display only requests from client ID #1.

So I'm using <asp:QueryStringParameter Name="client" QueryStringField="client" /> for query "EXEC getRequests @client".

Everything works properly when some client is specified. But don't - if not.

I tested my SP using SSMS - it works properly in both cases - when parameter is specified and when it isn't (NULL passed explicitly).

What have I do?

Thaumaturgy

Looks like you're opening yourself up to some pretty serious SQL injection attack vectors with this approach. – Siliqua

@womp: How am I opening? QueryStringParameter is being added in code-behind only for users with appropriate rights and after a number of checks. – Thaumaturgy

AH, if you're sanitizing it, then that's fine. It just looked from your question like you were using it directly. – Siliqua

@womp: I take only client ID (int) and pass it to SP. I'm sure this is safe to do. I don't do silly things like "SELECT ... WHERE ID=" + Request["client"] :) – Thaumaturgy

Answer 1

SqlDataSource won't fire if any of it's parameters are null, unless you specify otherwise:

<asp:SqlDataSource CancelSelectOnNullParameter="False" />

It might also be necessary to add a null default value to your querystring parameter:

<asp:QueryStringParameter Name="client" QueryStringField="client" DefaultValue="" ConvertEmptyStringToNull="True" />

Ziwot

Thank you very much! First option does what I need. – Thaumaturgy

That's a a really awkward default (i.e. it should fire with NULL params by default). I'm pretty sure NULL params to indicate 'everything' are very common. – Bronchia

Answer 2

You need to define a Default value to the parameter for those situations, for example:

<asp:QueryStringParameter Name="client" QueryStringField="client" DefaultValue="0"/>

and then in the SP you need verify if the client is 0, return all the clients, otherwise the specific one.

Marrilee

Is it possible to set default value to NULL (DBNull.Value) ? – Thaumaturgy

Hmm i dont think so. But is there a reason to use NULL instead of 0, -1 or something else? – Marrilee

For NULL it's easy to use SQL built-in function ISNULL(,). Values like 0 or -1 required additional CASE-WHEN-THEN statement into query – Thaumaturgy

With this url "View.aspx?client=" i think the parameter is automaticly converted to null, because its empty. Try this and give some feedback, – Marrilee

Thank you for your participation! :) Problem is solved. See below – Thaumaturgy