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

  • 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.

主站蜘蛛池模板: 莆田市| 河北省| 商洛市| 邯郸市| 澜沧| 建瓯市| 胶南市| 横峰县| 上虞市| 马尔康县| 宁波市| 中江县| 昌黎县| 奉贤区| 秦皇岛市| 章丘市| 沽源县| 阿图什市| 德惠市| 云安县| 望谟县| 黑水县| 红河县| 福建省| 壤塘县| 崇阳县| 高台县| 道孚县| 北票市| 平泉县| 申扎县| 平定县| 江城| 丰台区| 泸水县| 平原县| 大厂| 沁水县| 社旗县| 营口市| 襄城县|