How to Safe input order by with SQLCommand in VbNet using SQLServer

Question

Select cod,nom from tb_user where cod > @param0 order by @param1

    Dim mycod = 3
    Dim myorderby = "asc"

    Dim _adapter = New SqlDataAdapter
    cmd.CommandTimeout = timeout
    cmd.Connection = _conn
    cmd.CommandText = pSql
    cmd.CommandType = CommandType.Text

    Dim sqlParameter0 = New SqlParameter("@param0", mycod)
    cmd.Parameters.Add(sqlParameter0)
    Dim sqlParameter1 = New SqlParameter("@param1", myorderby)
    cmd.Parameters.Add(sqlParameter1)

    _adapter.SelectCommand = cmd

    _adapter.Fill(_ds, "result")

I know I must replace the @param0 by the value of my variable mycod to be safe. This is possible in the variables like the param0, but the @param1 where I put asc it gives me the following error:

the SELECT item identified  by the ORDER BY number 1 contains a variable as part of the expression identitying a column position

PS: By the error it is clear the SqlParameter is not the way to input this kind of order by. Is there a way to input this kind of query safely?

Not good but practice the whole system now is using ordering in the client side... — felipe, Jan 31 '19 at 22:06
My guess is you are allowing the user to order the results? Why is that bad practice to order client side? — Sean Lange, Jan 31 '19 at 22:08
No specific reson todo that, just design I made in that time... the user can select the orderby asc or orderby a column... just because it could be easier to manage the sql — felipe, Jan 31 '19 at 22:16

score 0 · Answer 1 · answered Jan 31 '19 at 22:09

0

You can do this by selectively ordering on the two columns.

Select cod,nom from tb_user 
where cod > @param0 
order by 
   case when @param1=1 then cod else 0 end, 
   case when @param1=2 then nom else 0 end

answered Jan 31 '19 at 22:09

TomC

2,759
1
7
16

should I do a case to every column? and the asc and desc? – felipe Jan 31 '19 at 22:18
Your example had only two columns in the result set. How many are you talking about? The technique will work fine regardless but could get kind of long! – TomC Jan 31 '19 at 23:09

score 0 · Answer 2 · answered Jan 31 '19 at 22:19

0

There are techniques to dynamically sort by a parameter but this can often lead to a significant slow down. I had a nightmare situation when I tried something similar. The query worked well most of the time, but the query plan created was completely inappropriate when using a different parameter value, and the results took forever to return..

Since you're binding to a DataSet, you should just sort on the DefaultView after you call Fill().

_ds.Tables(0).DefaultView.Sort = myorderby

answered Jan 31 '19 at 22:19

Daniel Gimenez

18,530
3
50
70

but this sorting on dataset can slow down my app? – felipe Jan 31 '19 at 22:26
Well *if* it does then your best option is using a ORM like EntityFramework. That way you're not trying to rig a query to do something it's not meant to do and you'll have the performance of running it on the server. But be sure your application is being slowed down before you throw this solution out. Also consider loading the data asynchronously so you don't block your application at all - https://stackoverflow.com/questions/38015089/how-to-use-async-methods-to-load-db-data-and-keep-ui-responsive. – Daniel Gimenez Jan 31 '19 at 22:35

How to Safe input order by with SQLCommand in VbNet using SQLServer

2 Answers2