Are there any differences between SQL Server and MySQL when it comes to preventing SQL injection?

Question

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?

Answer 1

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

Answer 2

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"-- '

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.