Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Prevention of SQL injection with PHP for SQL Server and without PDO [duplicate]

I can sanitize and validate my input as much as possible but that definitely doesn't cover everything and if I scrub hard enough, thoroughly enough, I will completely wipe away my input.

I realize there are a lot of posts out there about this topic but it seems like they always go back to PDO or Mysql (yes - even if someone posts about SQL Server, half the answers they receive suggest mysql_real_escape_string - crazy world). I cannot use either. Even as I type and the little "similar questions" appear on the right of my screen, I keep clicking on various links and nothing fully answers my question.

I am using SQL Server. I am using PHP 5.2.4. I cannot use PDO (because...? my boss said 'no' and that's enough reason).

Is there a way I could write a safe way to prepare my own query statements?

In the past, I have tried to build a statement like this in the PHP. (where $input_* variables are some form of user input or I pulled them out of something)

$query = "
    declare @varID  int
    declare @var1   int
    declare @var2   varchar(100) 

    set @varID = cast('$input_ID' as int)
    set @var1  = cast('$input_var1' as int)
    set @var2  = cast('$input_var2' as varchar(100)) 

    update table_name_goes_here
         set var1 = @var1,  
             var2 = @var2
         where ID = @varID;
    ";
 # $query is then executed 

but that can be vulnerable, too... obviously.... And the last thing I do is remove all necessary punctuation (sometimes I know they will have no reason to use certain characters)

But there has to be some other option... right? And mssql_bind only works for stored procedures, which is a definite option but I'm not sure if I want to volunteer to expand my responsibilities to include maintenance in the actual database by making insert/update procedures.

like image 801
gloomy.penguin Avatar asked Nov 03 '22 23:11

gloomy.penguin


1 Answers

I would say that "because the boss said 'no'" is a terrible reason. Tell him (her?) that he is wrong. I know little of PHP, but regardless of the language, the only foolproof way to prevent injection is through paramaterized queries, or stored procedures. If the only way to do that in PHP is to use PDO, then use PDO.

Here is your reasoning for using PDO: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

http://msdn.microsoft.com/en-us/magazine/cc163917.aspx

And why is there any SQL in the code at all? It is much easier to maintain if it is in the database, generally in the form of stored procedures.

like image 180
Dave Johnson Avatar answered Nov 09 '22 15:11

Dave Johnson