Jump to content
Invision Community
FORUMS BLOG/NEWS USER BLOGS USER MEDIA ADVERTS   ADD  MANAGE CHAT CLUBS & USER PERSONAL FORUMS LINK EXCHANGE
ANTIVIRUS & SPAM Antivirus Anti Spam Anti Spyware Free Virus Scanner Antivirus Comparison Antivirus for Android Antivirus for Mac Anti Spam for Android Anti Spyware for Android
Sign in to follow this  
davidtrump

Parameterized statements & Escaping

Recommended Posts

Mitigation
An SQL injection is a well known attack and easily prevented by simple measures. After an apparent SQL injection attack on TalkTalk in 2015, the BBC reported that security experts were stunned that such a large company would be vulnerable to it.

Parameterized statements
With most development platforms, parameterized statements that work with parameters can be used (sometimes called placeholders or bind variables) instead of embedding user input in the statement. A placeholder can only store a value of the given type and not an arbitrary SQL fragment. Hence the SQL injection would simply be treated as a strange (and probably invalid) parameter value.

In many cases, the SQL statement is fixed, and each parameter is a scalar, not a table. The user input is then assigned (bound) to a parameter.

Enforcement at the coding level
Using object-relational mapping libraries avoids the need to write SQL code. The ORM library in effect will generate parameterized SQL statements from object-oriented code.

Escaping
A straightforward, though error-prone way to prevent injections is to escape characters that have a special meaning in SQL. The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive blacklist of characters that need translation. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. For example, in PHP it is usual to escape parameters using the function mysqli_real_escape_string(); before sending the SQL query:

$mysqli = new mysqli('hostname', 'db_username', 'db_password', 'db_name');
$query = sprintf("SELECT * FROM `Users` WHERE UserName='%s' AND Password='%s'",
                  $mysqli->real_escape_string($username),
                  $mysqli->real_escape_string($password));
$mysqli->query($query);

This function prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. This function is normally used to make data safe before sending a query to MySQL.
PHP has similar functions for other database systems such as pg_escape_string() for PostgreSQL. The function addslashes(string $str) works for escaping characters, and is used especially for querying on databases that do not have escaping functions in PHP. It returns a string with backslashes before characters that need to be quoted in database queries, etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).
Routinely passing escaped strings to SQL is error prone because it is easy to forget to escape a given string. Creating a transparent layer to secure the input can reduce this error-proneness, if not entirely eliminate it.

Share this post


Link to post
Share on other sites
MALWARE & PROVIDER Anti Malware Hosting Shared Hosting Windows Hosting VOIP Phone Register Domain Website Builder Dedicated Server Fiber Optics Provider

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...