13

I am used to developing in PHP/MySQL and have no experience developing with SQL Server. I've skimmed over the PHP MSSQL documentation and it looks similar to MySQLi in some of the methods I read about.

For example, with MySQL I utilize the function mysql_real_excape_string(). Is there a similar function with PHP/SQL Server?

What steps do I need to take in order to protect against SQL injection with SQL Server?

What are the differences between SQL Server and MySQL pertaining to SQL injection prevention?

also - is this post accurate? is the escape string character for SQL Server a single quote?

rook
  • 66,304
  • 38
  • 162
  • 239
Derek Adair
  • 21,846
  • 31
  • 97
  • 134

9 Answers9

15

Use PDO and parameterized queries and every database engine looks almost the same.

rook
  • 66,304
  • 38
  • 162
  • 239
Arkh
  • 8,416
  • 40
  • 45
  • +1 for PDO. This is a good idea even if you don't consider the SQL injection prevention possibilities. (Or maybe even Zend_Db :) ) – Billy ONeal Apr 09 '10 at 16:02
10

Use parametrized queries with ADODB or PDO. These libraries know the best escape function to use based on the database it is connected to. They allow you to switch between mysql and ms-sql without introducing vulnerabilities.

SQL Injection for MySQL and MS-SQL are radically different.

SQL Injection for MS-SQL is much more serious. For one you can stack queries:

select * from `table` where id='1' ; drop table `table`;-- '

Escaping is totally different, addslashses() does not stop sql injection under MS-SQL. It uses a double quote system so this is an escaped query:

select * from table where test='trying to inject '' didn''t work!'

A hacker can also access cmd.exe using xp_cmdshell from a sql query. Make sure this privilege has been removed!

Under MySQL you can't stack, so its common to use union select (only works when injection into a select, otherwise you can use a sub-select, but you can't stack a drop/update/delete/insert on top of a select):

select somthing from table where 1 union select password from mysql.user

Escaping is done with back slashes, addslashes() works most of the time, but mysql_real_escape_string() is a lot better. 

select * from table where test='trying to inject \' didn\'t work!'

Also you want to disable file_priv otherwise a hacker might be able to drop a backdoor:

select test from table where name='jon' union select "<?php eval($_GET[e])?>" into outfile "/var/www/backdoor.php"-- '
rook
  • 66,304
  • 38
  • 162
  • 239
  • -1 for thinking addslashes is an acceptable escape technique for MySQL. – SoapBox Apr 09 '10 at 23:00
  • @SoapBox addslashes() does not stop sql injection for **MS-SQL**. addslashes() isn't very good for mysql eather, mysql_real_escape_string() should be used (http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string) – rook Apr 10 '10 at 00:09
  • FYI: multiple mysql statements can be executed at once using mysqli::multi_query. – grossvogel Oct 19 '10 at 19:14
  • @grossvogel yes and 99% of applications do not use this, and in almost 5 years of writing exploits i haven't been able to take advantage of it. – rook Oct 19 '10 at 19:20
2

No. There is nothing inherit in any database product to protect you against SQL injection because the problem is not rooted in the database. The problem is in the way outside applications formulate requests and send them to the database.

Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • I don't believe this answers the question that was asked. The OP seems to understand what SQL injection is and how it works. – Billy ONeal Apr 09 '10 at 15:46
  • It absolutely answers the question: Is there any difference. The answer is no. – Thomas Apr 09 '10 at 15:47
  • @Thomas: Okay, I guess so, but the OP is specifically referring to the PHP extensions mysqli and mssql. There is certainly a difference between using those two APIs. – Billy ONeal Apr 09 '10 at 15:48
  • `What steps do I need to take in order to protect against SQL injection with MSSQL?` - no? – Derek Adair Apr 09 '10 at 15:49
  • @Derek Adair: I believe he is referring to "Are there any differences between MSSQL and MySQL when it comes to preventing SQL injection?" @Thomas: Changed vote to +1. – Billy ONeal Apr 09 '10 at 15:50
  • @Billy ONeal - Correct. I'm answering the root question of the OP and I'm answering the question "What are the differences between MSSQL and MySQL pertaining to SQL injection prevention?". There are none because the database is not the source of the problem. – Thomas Apr 09 '10 at 15:51
2

it is not the tool that allows SQL injection attacks, it is the programmer and how they use it. both mysql and sql server allow you to get injected if you code incorrectly (blindly concatenate strings to create dynamic sql) and both provide parameter binding to avoid it.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • is parameter binding sufficient to prevent injection? – Derek Adair Apr 09 '10 at 15:55
  • yes, injection works by putting commands within the value that are appended to your command string when you build it by concatenation. By using parameter binding your command runs and uses the parameters only as values. – KM. Apr 09 '10 at 18:26
1

No, MSSQL provides no such function, and in Mysqli you shouldn't be using mysql_real_escape_string either. In both cases you should be using Prepared Statements or Stored Procedeures. I believe the PHP documentation provides ample explanation on how to use the MSSQL apis.

Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
  • the MSSQL extension of PHP unfortunately offers neither input sanitation *nor* prepared statements. – Michael Borgwardt Apr 09 '10 at 15:43
  • @Michael Borgwardt: Check the link. It does offer prepared statements - I linked to the function that creates/implements them. Though to be fair, MSSQL requires they be stored on the SQL server first. – Billy ONeal Apr 09 '10 at 15:44
  • @Billy ONeal: Those are stored procedures, slightly different from prepared statements, better use http://sqlsrvphp.codeplex.com/ or pdo. – VolkerK Apr 09 '10 at 15:45
  • Then it's a stored procedure, not a prepared statement... I guess there's some overlap though. – Michael Borgwardt Apr 09 '10 at 15:47
  • 2
    -1 for claiming that stored procedures have anything to do with protecting you against SQL injection. You can still be vulnerable to SQL injection using stored procedures. – Thomas Apr 09 '10 at 15:49
  • 2
    @Thomas: Did you even read the link? When you bind a parameter to the query it will be escaped correctly. – Billy ONeal Apr 09 '10 at 15:50
  • @Billy ONeal - I still stand by my statement that stored procedures have nothing to do with protecting you against SQL injection. However, I redacted my -1 because you do mention using prepared statements. – Thomas Apr 09 '10 at 15:53
  • 1
    @Billy ONeal - Remember that you can still call a sp using a string ("Exec foo 'arg1','arg2';DROP TABLE FOO"). What matters is not the sp but the way in which you call the sp. – Thomas Apr 09 '10 at 15:54
  • 1
    @Thomas: In general, you are correct. However, PHP's mssql extension handles escaping by binding to stored procedures. The database itself is not the reason Stored Procedures prevent injection, the MSSQL extension is the reason. When you call mssql_bind, the variable that is bound will be escaped. – Billy ONeal Apr 09 '10 at 15:54
  • 1
    FWIW If your sproc uses its parameters in a dynamic sql query then there is no automatic defense against injection attacks. – roufamatic Apr 09 '10 at 15:55
  • 1
    @roufamatic: Yes, that is correct. The mssql_bind function is what does the escaping, not the stored procedure. The reason the stored procedure offers protection is for that reason only -- it's not an inherent attribute of stored procedures. – Billy ONeal Apr 09 '10 at 16:00
  • @roufamatic is correct, you bind the parameter and it gets passed into the stored procedure properly. However if you use dynamic sql within that procedure to concatenate a SQL command, you are still open to any attack. – KM. Apr 09 '10 at 19:53
1

Parameterized queries are the way to go. The sqlsrv driver supports parameterized queries. Of course, this will only be useful to you if you are running PHP on Windows. On the chance that you are, there's more information (with examples) here: How and Why to Use Parameterized Queries.

Brian Swan
  • 66
  • 4
0

With MSSQL you can utilize stored procedures to reduce risk of sql injection. The stored procedures would accept input in various types, so it would be hard to pass in string with sql commands.

also check out http://msdn.microsoft.com/en-us/library/ms161953.aspx

Nertim
  • 380
  • 6
  • 15
  • Note that you could do this with MySQL as well, though I believe Transact-SQL is slightly more powerful for this sort of thing than MySQL's dialect. – Billy ONeal Apr 09 '10 at 15:44
0

Any database can be subject to SQL injection attacks. One way to help avoid them is to use Stored Procedures. Both MSSQL & MYSQL support stored procedures.

codingguy3000
  • 2,695
  • 15
  • 46
  • 74
  • You bind the parameter and it gets passed into the stored procedure properly. However if you use dynamic sql within that procedure to concatenate a SQL command, you are still open to any attack. – KM. Apr 09 '10 at 19:53
-1

Stored procedures. That's it. Deny access to any other operation other than executing stored procedures. This way you only allow a certain set of requests to be made to your database, thus eliminating any form of SQL Injection.

Both support Stored Procedures, BUT, in MSSQL they are easier to debug, because error messages are much clearer.

Alex
  • 14,338
  • 5
  • 41
  • 59
  • -1 stored procedures do not stop sql injection. You are probably thinking of parameterized queries. – rook Apr 10 '10 at 00:14
  • nope, stored procedures limit the data and data structures a user can get from the DB. By allowing access only to stored procedures you: 1. Deny any unauthorized changes to the database. 2. Deny the right to select sensible data like users. The only SQL injection possible in these scenarios are useless, because they would return ONLY data that is accessible from the interface that this "hacker" has full access to. I've designed not one of these systems and they all work perfectly. Before giving a Negative be sure you know what it's about. – Alex Apr 10 '10 at 11:28
  • Besides that, if you run it using the COM object ADODB.Command, you can even specify the command type, thus definitely avoiding any hacking scenarios. – Alex Apr 10 '10 at 11:30