SQL INJECTION PAGE 41

COMPUTER LANGUAGES HTML,C,C++.JAVA,.NET AND MULTIMEDIA basics and programs click home button

SQL INJECTION

SQL Injection 


If you take user input through a webpage and insert it into
 a SQL database, there's a chance that you have left yourself
 wide open for a security issue known as SQL Injection. 
This lesson will teach you how to help prevent this from 
happening and help you secure your scripts and SQL 
statements in your server side scripts such as PERL Script. 
Injection usually occurs when you ask a user for input, like
 their name, and instead of a name they give you a SQL 
statement that you will unknowingly run on your database. 
Never trust user provided data, process this data only after 
validation; as a rule, this is done by pattern matching. 
In the example below, the name is restricted to alphanumerical 
chars plus underscore and to a length between 8 and 20 chars
 (modify these rules as needed). 
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) 
{ 
   $result = mysql_query("SELECT * FROM CUSTOMERS  
                          WHERE name=$matches[0]"); 
} 
else  
{ 
   echo "user name not accepted"; 
} 
To demonstrate the problem, consider this excerpt: 
// supposed input 
$name = "Qadir'; DELETE FROM CUSTOMERS;"; 
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'"); 
The function call is supposed to retrieve a record from the 
CUSTOMERS table where the name column matches the name 
specified by the user. Under normal circumstances, $name 
would only contain alphanumeric characters and perhaps 
spaces, such as the string ilia. But here, by appending 
an entirely new query to $name, the call to the database 
turns into disaster: the injected DELETE query removes 
all records from CUSTOMERS. 
Fortunately, if you use MySQL, the mysql_query() function 
does not permit query stacking or executing multiple SQL 
queries in a single function call. If you try to stack 
queries, the call fails. 
CHAPTER 41
43 
However, other PHP database extensions, such as SQLite 
and PostgreSQL, happily perform stacked queries, executing 
all of the queries provided in one string and creating a 
serious security problem. 
Preventing SQL Injection: 
You can handle all escape characters smartly in scripting 
languages like PERL and PHP. The MySQL extension for PHP 
provides the function mysql_real_escape_string() to 
escape input characters that are special to MySQL. 
if (get_magic_quotes_gpc())  
{ 
  $name = stripslashes($name); 
} 
$name = mysql_real_escape_string($name); 
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'"); 
The LIKE Quandary: 
To address the LIKE quandary, a custom escaping mechanism must 
convert user-supplied ‘%’ and ‘_’ characters to literals. 
Use addcslashes(), a function that let's you specify a character range to escape. 
$sub = addcslashes(mysql_real_escape_string("%str"), "%_"); 
// $sub == \%str\_ 
mysql_query("SELECT * FROM messages  
             WHERE subject LIKE '{$sub}%'");