Prevention of SQL injection with PHP for SQL Server and without PDO

Question

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.

Just some suggestions for cleaning the data before putting it in your query: test the length of the data (min/max), test the value of the data (min/max), use a regex to validate, use the is_numeric() et. al. functions, use the ctype_* functions, use htmlentities(). Write a function/method that uses the tools that make sense and run all your data through it before sending it on to the database. Figure out some way to convince your boss that parameterized queries are a good thing. — Benny Hill, Apr 24 '13 at 21:32
@BennyHill I guess validation is the best and only route. He knows PDO would be a good thing but upgrading PHP as a whole is a higher priority to us right now than individual DB drivers. SQL Server is not something we commonly support, either. It will happen eventually... just can't do it right now... so I need the safest alternative that works with what I already have. — gloomy.penguin, Apr 24 '13 at 21:56

score 2 · Answer 1 · answered Apr 24 '13 at 21:31

2

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.

answered Apr 24 '13 at 21:31

Dave Johnson

825
16
27

Regarding @Dave Johnson "I would say that "because the boss said 'no'" is a terrible reason." - I totally agree with that, but sometimes when the boss puts their foot down you have to find another way or find another job. :) – Revent Apr 24 '13 at 21:39
@Revent - Yes, I have dealt with that. Unless the boss can somehow come up with a reason that trumps security issues, I would choose the new job route :) – Dave Johnson Apr 24 '13 at 21:46
I've used PDO and I like it and it is super easy... but I cannot use it and I cannot just walk out because of that. **Even if not perfect, is there really no one who can offer me a solution that doesn't deal with PDO (this answer) or MYSQL (answer below this one)?** – gloomy.penguin Apr 24 '13 at 21:50
I'm not suggesting you walk out. I'm saying you should convince your boss to let you use parameterized queries. I can think of no logical argument against it. Based on what I've seen, PDO, MySQL and Stored Procedures are your best options (it looks like you can do sprocs without PDO). Otherwise, it seems you have to just hope you account for all possibilities with data validation. – Dave Johnson Apr 24 '13 at 21:58
`And why is there any SQL in the code at all?` - Seriously...? I don't want to have to maintain things in someone else's database... I am busy enough and especially for just straight update. In fact, I have no idea how I could even do my job w/o at least a minimal amount of SQL in the PHP. I will make stored procedures or views when they are needed but I am not informed or included of constant changes to this DB so it makes it harder... – gloomy.penguin Apr 24 '13 at 22:05
Yes, seriously. When database changes require an SQL code change, you have to change it somewhere, be it in your PHP or in a stored procedure. In my experience it is easier to redeploy a stored procedure than a website. It also makes this entire discussion pointless, because you would be passing parameters to a stored procedure instead of direct SQL queries to a database. – Dave Johnson Apr 25 '13 at 20:51

score 0 · Accepted Answer · edited May 23 '17 at 12:12

You haven't answered the question "How do you intent to talk to the MS SQL database if PDO isn't allowed", but I assume there are the mssql_* functions to be used.

These do not have an escaping function readymade, but it seems they offer you to use prepared statements - which will do the job.

Otherwise you would have the security-relevant task to create an escaping function yourself. The character replacement is not really complicated when you first look at it, and you might be lucky to only have to cover your exact use case with a defined encoding. So this might really be as easy as looking up in the MSSQL manual which characters in a string are not allowed as a simple character, and how to escape them.

Be alerted though that you might miss edge cases, and if you can avoid it, I'd rather use the prepared statement feature.

Update: I misread the manual, mssql_execute() only calls stored procedures, not prepared statements. Can't you store procedures? Would be an easy way out. But I'd like to know how you are supposed to talk to the database anyways.

Update2: I found a link in a comment on php.net for mssql_bind pointing back to an SO answer about escaping: How to escape strings in SQL Server using PHP?

Prevention of SQL injection with PHP for SQL Server and without PDO

2 Answers2

Linked