Are there any differences between SQL Server and M

问题:

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?

回答1:

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

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

回答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.

回答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.

回答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.

回答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.

回答7:

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

回答8:

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.

回答9:

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.