Sql Injection, Security Threat Options

[NickTheGreek]

[title]SQL Injection[/title]

This is by far the most important worry for me regarding this forum...

With so much talent in modern hackers around i really do not know how much this could stand an organised attack...



SQL Injection is the most popular method to hacking a forum and taking control of it :

<p></p>
<p><b>SQL injection</b> is a <a href="http://en.wikipedia.org/wiki/Security_vulnerability" title="Security vulnerability">security vulnerability</a> that occurs in the <a href="http://en.wikipedia.org/wiki/Database" title="Database">database</a> layer of an <a href="http://en.wikipedia.org/wiki/Application_software" title="Application software">application</a>. Its source is the incorrect escaping of dynamically-generated <a href="http://en.wikipedia.org/wiki/String_literal" title="String literal">string literals</a> embedded in <a href="http://en.wikipedia.org/wiki/SQL" title="SQL">SQL</a>

statements. It is in fact an instance of a more general class of
vulnerabilities that can occur whenever one programming or scripting
language is embedded inside another.</p>

<h2>Example case of SQL injection</h2>

<p>Assume that the following code is embedded in an application. The value of the variable <i>userName</i>
is assigned from a user input parameter -- for example, the value of an
HTTP request variable or HTTP cookie. The following code naively
constructs a SQL statement by appending the user-supplied parameter to
a SELECT statement:</p>
<pre>statement := "SELECT * FROM users WHERE name = '" + userName + "';"
</pre>
<p>If the input parameter is manipulated by the user, the SQL statement
may do more than the code author intended. For example, if the input
parameter supplied is <i>a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%" as "userName</i>, the following SQL statement would be built by the code above:</p>
<pre>SELECT * FROM users WHERE name = 'a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%';
</pre>
<p>When sent to the database, this statement would be executed;
selecting data, dropping (deleting) the "users" table and selecting
data from a different table, most likely not data intended to be
displayed to web users. In essence, any data in the database available
to the user connecting to the database could be read and/or modified.</p>

Remediation

<h3>Application remediation</h3>
<p>SQL injection is easy to work around with in most <a href="http://en.wikipedia.org/wiki/Programming_languages" title="Programming languages">programming languages</a> that target web applications or offer functionality. In <a href="http://en.wikipedia.org/wiki/Perl" title="Perl">Perl</a> DBI, the <tt>DBI::quote</tt> method escapes special characters (assuming the variable <tt>$sql</tt> holds a reference to a DBI object):</p>

<pre>$query = $sql-&gt;prepare
(
"select * from users where name = "
.
$sql-&gt;quote($user_name)
);
</pre>
<p>Or one may use the placeholder feature (with automatic quoting) as follows:</p>
<pre>$query = $sql-&gt;prepare("select * from users where name = ?");
$query-&gt;execute($user_name);
</pre>
<p>In <a href="http://en.wikipedia.org/wiki/PHP" title="PHP">PHP</a>, there are different built-in functions to use for different DBMSes. For <a href="http://en.wikipedia.org/wiki/MySQL" title="MySQL">MySQL</a>, the equivalent is the built-in function <tt>mysql_real_escape_string</tt>:</p>

<pre>$query_result = mysql_query
(
"select * from users where name = \""
.
mysql_real_escape_string($user_name)
.
"\""
);
</pre>
<p>In the <a href="http://en.wikipedia.org/wiki/Java_programming_language" title="Java programming language">Java</a> programming language, the equivalent is the PreparedStatement class.</p>
<p>Instead of</p>
<pre>Connection con = (acquire Connection)
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("SELECT * FROM users WHERE name = '" + userName + "';");
</pre>
<p>use the following</p>
<pre>Connection con = (acquire Connection)
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM users WHERE name = ?");
pstmt.setString(1, userName);
ResultSet rset = pstmt.executeQuery();
</pre>
<p>In the <a href="http://en.wikipedia.org/wiki/Microsoft_.NET" title="Microsoft .NET">.NET</a> (or <a href="http://en.wikipedia.org/wiki/Mono_development_platform" title="Mono development platform">Mono</a>) programming language "<a href="http://en.wikipedia.org/wiki/C_Sharp" title="C Sharp">C#</a>",
the equivalent are the ADO.NET SqlCommand (for Microsoft SQL Server) or
OracleCommand (for Oracle's database server) objects. The example below
shows how to prevent injection attacks using the SqlCommand object. The
code for other ADO.NET providers is very similar, but may vary slightly
depending on the specific implementation by that provider vendor.</p>

<p>Instead of</p>
<pre>using( SqlConnection con = (acquire connection) ) {
con.Open();
using( SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE name = '" + userName + "'", con) ) {
using( SqlDataReader rdr = cmd.ExecuteReader() ){
...
}
}
}
</pre>
<p>use the following</p>
<pre>using( SqlConnection con = (acquire connection) ) {
con.Open();
using( SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE name = @userName", con) ) {

cmd.Parameters.Add("@userName", userName);

using( SqlDataReader rdr = cmd.ExecuteReader() ){
...
}
}
}
</pre>

<h3>Database remediation</h3>
<p>Setting security privileges on the database to the least-required is
a simple remediation. Few applications will require that the
application user has delete rights to a table or database.</p>

<p>Most databases also offer the capability of preparing SQL statements at the database layer via <a href="http://en.wikipedia.org/wiki/Stored_procedure" title="Stored procedure">stored procedures</a>.
Rather than using an application layer to construct SQL dynamically,
stored procedures encapsulate reusable database procedures that are
called with <a href="http://en.wikipedia.org/wiki/Datatype" title="Datatype">typed</a>
parameters. This provides several security advantages: by
parameterizing input parameters and type-enforcing them, user input is
effectively filtered. In addition, most databases allow stored
procedures to execute under different security privileges from the
database user. For instance, an application would have execute access
to a stored procedure, but no access to the base tables. This restricts
the ability of the application to do anything beyond the actions
specified in the stored procedures.</p>
<p>It is also important to note that the standard query method of the <a href="http://en.wikipedia.org/wiki/MySQL" title="MySQL">MySQL</a> <a href="http://en.wikipedia.org/wiki/C" title="C">C</a>
client library will not allow more than one query in one input,
preventing the multi-statement attack described above. However, even
benign user input containing escape characters (eg single-quotes) could
still cause the application to crash from a bad SQL syntax. Even some
attacks are possible, for example consider a website showing a list of
items for a known username. The query executed is:</p>
<pre> SELECT * from items where username='$username';
</pre>

<p>An attacker could use a specially crafted username to expose all items belonging to all users:</p>
<pre> $username = "' or username is not null or username='";
</pre>
<p>Which would result in the following query:</p>
<pre> SELECT * from items where username='' or username is not null or username='';
</pre>

<h2>See also</h2>

<ul>
<li><a href="http://en.wikipedia.org/wiki/Code_injection" title="Code injection">Code injection</a>

<h2>External links</h2>
<ul>
<li><a href="http://www.derkeiler.com/Mailing-Lists/securityfocus/secprog/2001-07/0001.html" class="external text" title="http://www.derkeiler.com/Mailing-Lists/securityfocus/secprog/2001-07/0001.html">Abusing Poor Programming Techniques in Webserver Scripts via SQL Injection</a></li>
<li><a href="http://www.ngssoftware.com/papers/advanced_sql_injection.pdf" class="external text" title="http://www.ngssoftware.com/papers/advanced sql injection.pdf">Advanced SQL Injection Attacks</a> by <a href="/w/index.php?title=Chris_Anley&amp;action=edit" class="new" title="Chris Anley">Chris Anley</a></li>

<li><a href="http://imperva.com/application_defense_center/glossary/sql_injection.html" class="external text" title="http://imperva.com/application defense center/glossary/sql injection.html">What is SQL injection?</a></li>
<li><a href="http://www.linuxsecurity.com/docs/Hack-FAQ/computers/sql-injection-attack.shtml" class="external text" title="http://www.linuxsecurity.com/docs/Hack-FAQ/computers/sql-injection-attack.shtml">What is an SQL Injection Attack / Vulnerability?</a></li>
<li>Article "<a href="http://governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php" class="external text" title="http://governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php">SQL Injection: Modes of Attack, Defence, and Why It Matters</a>" by <a href="/w/index.php?title=Stuart_McDonald&amp;action=edit" class="new" title="Stuart McDonald">Stuart McDonald</a></li>
<li>Article "<a href="http://informit.com/articles/article.asp?p=30124&amp;seqNum=3" class="external text" title="http://informit.com/articles/article.asp?p=30124&amp;seqNum=3">SQL Server Attacks: Hacking, Cracking, and Protection Techniques</a>" by <a href="/w/index.php?title=Seth_Fogie&amp;action=edit" class="new" title="Seth Fogie">Seth Fogie</a> and <a href="/w/index.php?title=Cyrus_Peikari&amp;action=edit" class="new" title="Cyrus Peikari">Cyrus Peikari</a></li>

<li>Article "<a href="http://securityfocus.com/infocus/1644" class="external text" title="http://securityfocus.com/infocus/1644">SQL Injection and Oracle, Part One</a>" by <a href="/w/index.php?title=Pete_Finnigan&amp;action=edit" class="new" title="Pete Finnigan">Pete Finnigan</a></li>
<li>Article "<a href="http://www.sitepoint.com/article/sql-injection-attacks-safe" class="external text" title="http://www.sitepoint.com/article/sql-injection-attacks-safe">SQL Injection Attacks - Are You Safe?</a>" by <a href="/w/index.php?title=Mitchell_Harper&amp;action=edit" class="new" title="Mitchell Harper">Mitchell Harper</a></li>
<li>Article "<a href="http://winnetmag.com/Article/ArticleID/42216/42216.html" class="external text" title="http://winnetmag.com/Article/ArticleID/42216/42216.html">Protecting Against SQL Injection</a>" by <a href="/w/index.php?title=Umachandar_Jayachandran&amp;action=edit" class="new" title="Umachandar Jayachandran">Umachandar Jayachandran</a></li>
<li>Article "<a href="http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/" class="external text" title="http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/">Stop SQL Injection Attacks Before They Stop You</a>" by <a href="/w/index.php?title=Paul_Litwin&amp;action=edit" class="new" title="Paul Litwin">Paul Litwin</a></li>

<li>"<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp" class="external text" title="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp">Injection Protection</a>"</li>
<li>"<a href="http://governmentsecurity.org/articles/SQLinjectionBasicTutorial.php" class="external text" title="http://governmentsecurity.org/articles/SQLinjectionBasicTutorial.php">SQL injection Basic Tutorial</a>"</li>
<li>"<a href="http://citeseer.ist.psu.edu/641328.html" class="external text" title="http://citeseer.ist.psu.edu/641328.html">SQLrand: Preventing SQL Injection Attacks</a>" by <a href="/w/index.php?title=Stephen_W._Boyd&amp;action=edit" class="new" title="Stephen W. Boyd">Stephen W. Boyd</a> and <a href="/w/index.php?title=Angelos_D._Keromytis&amp;action=edit" class="new" title="Angelos D. Keromytis">Angelos D. Keromytis</a></li>
<li>"<a href="http://www.cgisecurity.com/questions/sql.shtml" class="external text" title="http://www.cgisecurity.com/questions/sql.shtml">What is SQL Injection?</a>" By CGISecurity.com</li>

<li>"<a href="http://www.cgisecurity.com/questions/blindsql.shtml" class="external text" title="http://www.cgisecurity.com/questions/blindsql.shtml">What is Blind SQL Injection?</a>" By CGISecurity.com</li>
<li><a href="http://www.windowsitpro.com/Article/ArticleID/46379/46379.html" class="external text" title="http://www.windowsitpro.com/Article/ArticleID/46379/46379.html">Avoid SQL injection</a>

[source]Wikipedia[/source]