#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0

    Prevent sql injection calling stored procedure


    Hello All,

    I am new to the forum and Perl. Hope someone can help. I need expert advice on preventing sql injection when calling stored procedures. I think I have everything done the right way but to be sure I am need your feedbacks. Looking at my code, can you please tell me if it's still possible to have sql injection.

    create procedure RET_IMAGES ( @id int)
    as
    select * from table
    where ID = @id
    #-----------------------------------------------------------
    $sql = "execute RET_IMAGES $id";
    $y->dbcmd($sql);
    $status = $y->dbsqlexec;

    Is it possible to execute multiple statements? For example,
    $id = 10; drop table tablename;
    so --> execute RET_IMAGES 10; drop table tablename;

    =============================================
    create procedure RET_FIPS (@state char(2))
    as
    select * from table where STATE = @state
    #------------------------------------------------------------
    $dbproc->dbcmd("RET_FIPS '$State'");
    $dbproc->dbsqlexec;
    $dbproc->dbresults;

    The same here, is it possible to execute multiple sql statements?


    Thank you in advance,
    btran
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,873
    Rep Power
    1225
    If any of the vars used in sql statements comes directly from user input, then it's possible to have sql injection. You need to test all user supplied data to make sure it is acceptable.

    For your second question, are you referring to executing the same statement multiple times with different $State values? Or are you wanting to execute various different sql statements?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Thanks you for your prompt reply. I was afraid of that. So it seems like having proper/safely written stored procedure still cannot prevent sql injection. Everywhere I read it says that how you prevent sql injection.

    For my second question, which basically the same as previous question but the execute on stored procedure is done differently. Does that make any different?
    $sql = "execute RET_IMAGES $id";
    $y->dbcmd($sql);
    $status = $y->dbsqlexec;

    versus....

    $dbproc->dbcmd("RET_FIPS '$State'");
    $dbproc->dbsqlexec;
    $dbproc->dbresults

    Thanks
    btran

    Originally Posted by FishMonger
    If any of the vars used in sql statements comes directly from user input, then it's possible to have sql injection. You need to test all user supplied data to make sure it is acceptable.

    For your second question, are you referring to executing the same statement multiple times with different $State values? Or are you wanting to execute various different sql statements?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,873
    Rep Power
    1225

    Exclamation


    First, select statements like you're using in the example are typically not much of a concern with sql injection. It's the inserts that are more vulnerable. However, it's probably possible to imbed an insert statement inside a select statement.

    I have not worked with stored procedures, so I can't say how secure they are in this respect.

    The most important thing to remember is that ALL user supplied data should be considered tainted and before it is used, you should untaint it. If you do that, the possibility for sql injection, practically speaking, goes down to almost nill.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    By any chance you know a good resource to untaint user input? Googling helps but hope you can point me to good place.

    Thanks

    Originally Posted by FishMonger
    First, select statements like you're using in the example are typically not much of a concern with sql injection. It's the inserts that are more vulnerable. However, it's probably possible to imbed an insert statement inside a select statement.

    I have not worked with stored procedures, so I can't say how secure they are in this respect.

    The most important thing to remember is that ALL user supplied data should be considered tainted and before it is used, you should untaint it. If you do that, the possibility for sql injection, practically speaking, goes down to almost nill.

IMN logo majestic logo threadwatch logo seochat tools logo