Introduction
Previously I
written around 7 computer security related articles (mostly on web security and
cyber security), and this is my first article or post on RDBMS data or database
application security.
In this article I
will explain in short what are sql injections and simple rules to prevent sql
injections, I want to say thanks to
my friend Vaibhav Shringi (DB expert
and IITian) for his help to prepare this post.
What is SQL injection?
SQL injection is a
type of security exploit in which the attacker adds SQL code to
a Web form input box or any other manner to gain access to resources or make
changes to data.
How to Prevent SQL Injection
Attacks?
Simple Rules to Prevent SQL
Injections
1. Sanitize the Input:
It's absolutely vital to sanitize user inputs to insure that
they do not contain dangerous codes, whether to the SQL server or to HTML
itself.
We should always attempt to allow only required characters approach
not to stuff “Bad characters”
There is really no benefit in allowing characters that could
not be valid, and rejecting them early - presumably with an error message - not
only helps forestall SQL Injection, but also catches mere typos early rather
than stores them into the database.
Better prevention: White List Input Validation
2. Escape/Quotesafe the input:
We can’t sanitize the inputs which allows special characters
eg. “Bill O'Reilly”
is a valid name.
Always use QUOTENAME() function in SQL statements if user
input are required in In-line queries.
3. Use Bound Parameters:
Though quote-safing is a good mechanism, we're still in the
area of "considering user input as SQL", and a much better approach
exists: bound
parameters, which are supported by essentially all database programming
interfaces.
Example:
PreparedStatement ps = connection.prepareStatement(
"SELECT email FROM member WHERE name =
?");
ps.setString(1,
formField);
ResultSet rs
= ps.executeQuery();
This is probably the single most important step one can take
to secure a web application.
4. Limit database permissions
and segregate users:
The web application ought to use a database connection with
the most limited rights possible: query-only access to the members table, and no access to any other table. If required
can move to higher rights after successful login.
It prevents unauthorized updates/delete/drop operations.
We should not use “SA” or same level rights’ users.
5. Use stored procedures for
database access:
Use stored procedures for performing access on the
application's behalf, which can eliminate SQL entirely. By encapsulating the
rules for a certain action - query, update, delete, etc. - into a single
procedure, it can be tested and documented on a standalone basis and business
rules enforced.
Example
DB SP:
Create PROCEDURE [dbo].[ReadUserDetails]
-- Add the parameters for the stored procedure here
@userName varchar(50)
AS
BEGIN
SELECT * FROM [UserDetails] where userName= @userName
END
C# code that’s use SP:
public static DataTable
ExecuteSelectCommand(string StoredProcedureName)
{
// SqlCommand cmd = null;
var table = new DataTable();
using (var con = new SqlConnection(GetConnectionString()))
{
con.Open();
using (var cmd = new SqlCommand(StoredProcedureName,
con))
{
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText
= commandName;
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
}
return table;
}
6. Isolate the webserver:
Even having taken all these mitigation steps, it's
nevertheless still possible to miss something and leave the server open to
compromise.
Isolated webserver with limited network pinholes can assure
limited access to other servers in case of full webserver control.
7. Configure error reporting:
The default error
reporting for some frameworks includes developer debugging information, and
this cannot be shown to outside users. Imagine how much easier a time
it makes for an attacker if the full query is shown, pointing to the syntax
error involved.
This
information is useful to developers, but it should be restricted - if
possible - to just internal users.
धन्यवाद मित्रो !!
Thanks Friends