Search Using Stored Procedure

  • Hi to all 🙂

    I want to have a result like this ---- SELECT * FROM TestMyView WHERE firstname = 'test5'

    but my code will produce like this ---- @sqlquery firstname = @firstname

    what is wrong with this??...

    use Biography

    Declare @firstname varchar(50),@middlename varchar(50),@lastname varchar(50), @sex varchar(50),@status varchar(50),@SqlQuery varchar(max),@bioID int, @SqlQueryFirstName varchar(max)

    SET @bioID = 13

    SET @firstname = 'test5'

    SET @middlename = 'test'

    SET @lastname = 'tes'

    SET @sex = 'Female'

    SET @status = 'single'

    SET @sqlquery = ''

    SET @SqlQueryFirstName = ''

    SET @sqlquery = 'SELECT * FROM TestMyView WHERE '

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    print(@SqlQueryFirstName)

    thanks 🙂

  • Probably the biggest thing wrong with it is that it's very prone to SQL Injection. Please see the following article for how to do "Catch All" queries without the chance of SQL Injection.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    Instead of writing SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname' this use below code.

    SET @SqlQueryFirstName = @sqlquery + 'firstname = @firstname'

    i.e. remove single quotes from @sqlquery.

  • One correction :

    SET @SqlQueryFirstName = @sqlquery + ' firstname = ' + @firstname

  • Instead of this:

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    Write this:

    SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (9/23/2013)


    Instead of this:

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    Write this:

    SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

    Instead of this, read the article that Jeff suggested. The approach of executing parameters is VERY VERY VERY bad idea.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/23/2013)


    kapil_kk (9/23/2013)


    Instead of this:

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    Write this:

    SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

    Instead of this, read the article that Jeff suggested. The approach of executing parameters is VERY VERY VERY bad idea.

    Hi Sean 🙂

    Looks very interesting to me....can you give a link to me regarding "The approach of executing parameters is VERY VERY VERY bad idea"..??..

    Thanks my friend 🙂 Cheers!!!

  • Young Jedi,

    Read these:

    http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/

    http://www.sqlservercentral.com/articles/Editorial/77168/

    http://www.sqlservercentral.com/articles/sql+injection/65129/

    I would consider dynamic SQL as an absolute last resort. Not a tool to be used by ex-Access programmers (I am one!) that have moved on to SQL Server. If the articles above don't scare you, ask your boss what would happen if you let someone run something like that...

  • pietlinden (9/23/2013)


    Young Jedi,

    Read these:

    http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/

    http://www.sqlservercentral.com/articles/Editorial/77168/

    http://www.sqlservercentral.com/articles/sql+injection/65129/

    I would consider dynamic SQL as an absolute last resort. Not a tool to be used by ex-Access programmers (I am one!) that have moved on to SQL Server. If the articles above don't scare you, ask your boss what would happen if you let someone run something like that...

    thanks piet 🙂

    But what is the meaning of "Young Jedi"? hehe

  • enriquezreyjoseph (9/23/2013)


    Sean Lange (9/23/2013)


    kapil_kk (9/23/2013)


    Instead of this:

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    Write this:

    SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

    Instead of this, read the article that Jeff suggested. The approach of executing parameters is VERY VERY VERY bad idea.

    Hi Sean 🙂

    Looks very interesting to me....can you give a link to me regarding "The approach of executing parameters is VERY VERY VERY bad idea"..??..

    Thanks my friend 🙂 Cheers!!!

    How about this one?

    http://bobby-tables.com/[/url]

    If that doesn't explain it how about this simple code example.

    --First we need to setup a table

    create table MyLoginTable

    (

    LoginID int identity primary key,

    UserName varchar(100),

    UserPassword char(36)

    )

    insert MyLoginTable

    select 'JModen', 'JModenPassword' union all

    select 'GShaw', 'GShawPassword' union all

    select 'SJones', 'SJonesPassword'

    go

    --Now we need to create a proc to pass our parameters

    create proc MyLoginProc

    (

    @UserName varchar(100),

    @Password varchar(40)

    ) as

    declare @SQL varchar(200)

    set @SQL = 'select * from MyLoginTable

    where UserName = ''' + @UserName

    + ''' and UserPassword = ''' + @Password + ''''

    print @SQL

    exec( @SQL)

    go

    --This looks pretty harmless. Why is executing parameters such a bad idea?

    exec MyLoginProc 'jmoden', 'JModenPassword'

    --what happens when you run this one?

    exec MyLoginProc ''' or 1 = 1--', ''

    --How about this one? I just cleaned up the proc and the table from your database by using parameters to a proc.

    exec MyLoginProc ''' or 1 = 1;drop proc MyLoginProc; drop table MyLoginTable;--', ''

    Still not convinced that executing parameters directly is a bad idea? Check out the article I suggested from Gail. It shows you how to use dynamic sql and keep it safe from sql injection.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/24/2013)


    enriquezreyjoseph (9/23/2013)


    Sean Lange (9/23/2013)


    kapil_kk (9/23/2013)


    Instead of this:

    SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

    Write this:

    SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

    Instead of this, read the article that Jeff suggested. The approach of executing parameters is VERY VERY VERY bad idea.

    Hi Sean 🙂

    Looks very interesting to me....can you give a link to me regarding "The approach of executing parameters is VERY VERY VERY bad idea"..??..

    Thanks my friend 🙂 Cheers!!!

    How about this one?

    http://bobby-tables.com/[/url]

    If that doesn't explain it how about this simple code example.

    --First we need to setup a table

    create table MyLoginTable

    (

    LoginID int identity primary key,

    UserName varchar(100),

    UserPassword char(36)

    )

    insert MyLoginTable

    select 'JModen', 'JModenPassword' union all

    select 'GShaw', 'GShawPassword' union all

    select 'SJones', 'SJonesPassword'

    go

    --Now we need to create a proc to pass our parameters

    create proc MyLoginProc

    (

    @UserName varchar(100),

    @Password varchar(40)

    ) as

    declare @SQL varchar(200)

    set @SQL = 'select * from MyLoginTable

    where UserName = ''' + @UserName

    + ''' and UserPassword = ''' + @Password + ''''

    print @SQL

    exec( @SQL)

    go

    --This looks pretty harmless. Why is executing parameters such a bad idea?

    exec MyLoginProc 'jmoden', 'JModenPassword'

    --what happens when you run this one?

    exec MyLoginProc ''' or 1 = 1--', ''

    --How about this one? I just cleaned up the proc and the table from your database by using parameters to a proc.

    exec MyLoginProc ''' or 1 = 1;drop proc MyLoginProc; drop table MyLoginTable;--', ''

    Still not convinced that executing parameters directly is a bad idea? Check out the article I suggested from Gail. It shows you how to use dynamic sql and keep it safe from sql injection.

    Thank you sean :-)..your the champion....you made my day (-:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply