官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 318字
  • 2021-06-24 14:38:14

Security

One reason to use parameterized queries is for security. Using a properly formatted parameterized query can protect against SQL injection attacks. A SQL injection attack is where a malicious user can execute database code (in this case, T-SQL) on a server by appending it to a data-entry field in the application. As an example, imagine we have an application that contains a form that asks the user to enter their name into a text box. If the application were to use an ad hoc statement to insert this data into the database, it would generally concatenate a T-SQL string with the user input, as in the following code:

DECLARE @sql nvarchar(MAX);
SET @sql = N'INSERT Users (Name) VALUES (''' + <user input> + ''');';
EXECUTE (@sql);

A malicious user might enter the Bob'); DROP TABLE Users; -- value into the text box.

If this is the case, the actual code that gets sent to SQL Server would look like the following:

INSERT Users (Name) VALUES ('Bob'); DROP TABLE Users; --');

This is valid T-SQL syntax that would successfully execute. It would first insert a row into the Users table with the Name column set to 'Bob', then it would drop the Users table. This would of course break the application, and unless there was some sort of auditing in place, we would never know what happened.

Let's look at this example again using a parameterized query. The code might look like the following:

EXECUTE sp_executesql @stmt = N'INSERT Users (Name) VALUES (@name)', @params = N'@name nvarchar(100)', @name = <user input>

This time, if the user were to send the same input, rather than executing the query that the user embedded in the string, the SQL Server will insert a row into the Users table, with the Name column set to ('Bob'); DROP TABLE Users; --'. This would obviously look a bit strange, but it wouldn't break the application or breach security.

主站蜘蛛池模板: 维西| 米泉市| 隆林| 永康市| 商丘市| 瑞金市| 滁州市| 宁陕县| 杨浦区| 荣昌县| 祁门县| 陆丰市| 西平县| 恩施市| 博爱县| 和田县| 苏尼特右旗| 同仁县| 秀山| 鄂尔多斯市| 房产| 高淳县| 广饶县| 江口县| 武宣县| 五河县| 平安县| 沙河市| 柯坪县| 湟源县| 蓝山县| 会昌县| 富阳市| 新化县| 富蕴县| 赣榆县| 武威市| 汝阳县| 肃宁县| 绥江县| 雅江县|