Re: how to code to avoid SQL insertion attacks

From: Chris Smith (cdsmith_at_twu.net)
Date: 02/22/05 

Date: Mon, 21 Feb 2005 16:53:30 -0700

steve <me@me.com> wrote:
> However i see only 1 reasonable reply, and that is to use stored procedures,
> and call outs.

Stored procedures have absolutely nothing to do with SQL injection
attacks, except for the incidental detail that in JDBC,
CallableStatement is a subtype of PreparedStatement and implements the
same parameterized query mechanism. If you try to call a stored
procedure without CallableStatement or PreparedStatement -- something
that's quite possible in most databases -- then you are just as
vulnerable to SQL injection as someone executing SQL select statements.

On the other hand, you could certainly stand to learn something about
JDBC and prepared statements. One of the fundamental concepts of
prepared statements is that you are responsible only for providing the
data, and the database driver guarantees that it will be passed properly
to the DBMS by including it in SQL. How that happens is implementation-
specific, but one of several possible techniques is to do quoting of
special characters, making SQL injection attacks null and void. Another
possibility is to actually send the data in separate binary streams
according to a vendor-specific wire protocol for statement parameters,
which is practically speaking even more effective at solving SQL
injection, since it leaves less room for driver bugs to open security
holes.

Running SQL code as unprivileged accounts is also an effective layered
security technique to provide extra protection against injection
attacks. However, it makes for a very poor primary solution; and your
muddying the waters with all that nonsense about stored procedures
creates just enough confusion for someone to open up a security hole by
accident. Bad job.

I'll leave you to read other replies on why your attacks against a
system using prepared statements will fail. Lee and Antii are exactly
right on this matter. One nit, though:

> 1. String sql ="Select * from client_code_java where ?=null";
> 2. String sql ="Select * from client_code_java where ?<>null";

Technically, the two prepared statements above are actually valid.
(However, in ANSI SQL they will always return zero rows because
comparisons with NULL yield NULL rather than true or false.) However,
you can't plug in a column name there; only a literal value. This is,
of course, incredibly useless, so your queries are quite valid but still
unlikely to be correct.

> now consider how you would hack:
>
> String The_qry ="{ call
> external_user.fgfdgfddfg.asa(?,?,?,?,?,?,?,?,?,?,?,?,?)}";

You're right; you can't. That's because you're essentially using a
prepared statement there. The unprivileged user helps as well.
However, the lame attempt at obfuscation for security purposes convinces
me of two things:

1. You're never coming near a database schema I have to work on if I
have any say whatsoever on the matter.

2. You are obviously not qualified to be making security decisions in
any kind of critical environment.

Nevertheless, notice that if you write (fixing your naming convention
issues):

    String theQuery = "{call external_user.fgfsgfddfg.asa("
        + firstField + "," + secondField ... + ")}";

Then you would be using the same stored procedure but be quite
vulnerable to an SQL injection attack, leaving only your half-way
solution with unprivileged accounts to prevent a complete compromise of
the system.

Are you starting to understand yet? Your code was secure because you
used a parameterized query, NOT because you used a stored procedure. If
you had used a parameterized query without a stored procedure, it would
have been equally secure. If you had used a stored procedure without a
parameterized query, it would have been broken. Stored procedures don't
prevent you from writing secure code, but they are completely irrelevant
to whether you write secure code or not. 

-- 
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

Relevant Pages

  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Triple Whammy Newbie Question - abstracting connections/commands
    ... performing all of the necessary validation up front. ... So an SQL injection would never ... >>or stored procedures and create your SQL statements on the fly. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Injection Prevention
    ... I was assuming the usage of command and parameter objects from the client side. ... I did mention, however, "Provided you don't use dynamic SQL in your stored procedures". ... > vulnerable to sql injection. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I was assuming the usage of command and parameter objects from the client side. ... I did mention, however, "Provided you don't use dynamic SQL in your stored procedures". ... > vulnerable to sql injection. ...
    (microsoft.public.dotnet.security)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)