tsql - Parameterized SQL with dynamic WHERE column -

-

i trying write simple filtering, user can input column filter , value. tricky part dynamically choosing column filter.

i've found couple solutions on web , not sure implement. preference lean toward performance instead of maintainability. opinions appreciated.

let's assume have table "t", has 5 varchar columns: "c1", "c2", "c3", "c4", , "c5".

solution 1 - easy way

i use dynamic sql. on lines of:

declare @sql varchar(max) = 'select * t ' + @columnname + ' = ''' + @columnvalue + ''';' exec (@sql);

which come out like:

select * t c1 = 'asdf' ;

i don't want use solution following 2 reasons. i'm including simple point of reference before going down rabbit hole.

  1. it not guard against sql injection.
  2. even if parameterize columnvalue, have 5 different execution plans cached each of 5 columns since cannot parameterize @columnname.

solution 2 - or's

could use series of or's 2 parameters. let's say:

@columnname = 'c1' @columnvalue = 'asdf'

then sql become:

select * t (@columnname = 'c1' , c1 = @columnvalue)   or (@columnname = 'c2' , c2 = @columnvalue)   or (@columnname = 'c3' , c3 = @columnvalue)   or (@columnname = 'c4' , c4 = @columnvalue)   or (@columnname = 'c5' , c5 = @columnvalue)   or (@columnname null , 0 = 0) ;

i try stay away using or when possible. remember reading somewhere suffers performance issues, i'm no dba , can't up. thoughts?

solution 3 - coalesce

this solution relies on having parameter each column. parameters on lines of:

@c1 = 'asdf'; @c2 = null; @c3 = null; @c4 = null; @c5 = null;

sql comes out to:

select * t c1 = coalesce(@c1, c1)   , c2 = coalesce(@c2, c2)   , c3 = coalesce(@c3, c3)   , c4 = coalesce(@c4, c4)   , c5 = coalesce(@c5, c5) ;

does have opinion on method implement? i'm leaning towards coalesce, have no hard numbers or experience on matter. maybe there better way of doing things?

the safest way:

declare @sql nvarchar(max) = n'select * dbo.t '   + quotename(@columnname) + ' = @columnvalue;';  exec sp_executesql @sql, n'@columnvalue varchar(255)', @columnvalue;

to guard further sql injection, first check:

if @columnname not in (n'c1',n'c2',n'c3',n'c4',n'c5') begin   raiserror('nice try! %s not valid.', 11, 1, @columnname);   return; end

or @habo suggested, against sys.columns catalog view:

if not exists  (    select 1 sys.columns name = @columnname      , [object_id] = object_id('dbo.t') ) begin   raiserror('nice try! %s not valid.', 11, 1, @columnname);   return; end

especially when combined optimize ad hoc workloads, ok have 5 different execution plans - since are, after all, 5 different queries optimize differently based on indexes on different columns, distribution of data within columns, etc.

your or , coalesce versions - unless pay compilation hit every single time - stuck using same plan no matter column provided, therefore may work situations, not others. , plan gets not based on what's best parameter sent first.

also, if concerned performance, maybe don't use select * - if don't need of columns. if do, never know when adds blob or geometry or xml or other expensive column table, , code retrieves though doesn't care it.


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more