Results 1 to 10 of 10

Thread: SQL Injection

  1. #1
    Senior Member
    Join Date
    Jan 2004
    Location
    Hawaii
    Posts
    350

    SQL Injection

    Okay, my boss asked me to figure this one out. Keep in mind I know nothing of databases or SQL.

    They're writing an app that does something for contractors..handling work orders and such. Well, the find feature allows them to enter a binary operator (AND OR NOR etc) a string (address maybe), and a logical operator (= < > !=).

    This string is then all put together with a SQL command or something, and passed to the server. He wants to make sure that someone can't enter SQL commands in the text box and 'drop a table' or something. He wants me to find out how to safeguard against that.


    Any ideas?

    A_T
    Geek isn't just a four-letter word; it's a six-figure income.

  2. #2
    Senior Member
    Join Date
    Jun 2003
    Posts
    188
    Do the followng:-

    1. Check for special chars like = <> etc at each user input.
    2. Make sure the logic in the app is correct and cannot be circumvented.
    3. If you are wrtinig a web application, do not trust the urls and check each
    request.

  3. #3
    I guess one of the major questions to ask here is what language are you writing this in? Warlock made a good point in doing validation for characters. There are plenty of javascript codes out there that can help you out with that. Here's somewhere you may want to start looking:

    Click this link
    And so at last the beast fell and the unbelievers rejoiced. But all was not lost, for from the ash rose a great bird. The bird gazed down upon the unbelievers and cast fire and thunder upon them. For the beast had been reborn with its strength renewed, and the followers of Mammon cowered in horror. -from The Book of Mozilla, 7:15

  4. #4
    Jaded Network Admin nebulus200's Avatar
    Join Date
    Jun 2002
    Posts
    1,356
    AFAIK Javascript can NOT be trusted for any kind of input validation because Javascript runs client side...

    Any checking of input will have to be handled server side (if you want to use Javascript for an initial pass thats fine, but your server app shouldn't trust the data). Since you application seems to require that some SQL special characters have to be passed and could be used as input, there are a few ways you could approach the problem:

    1) Do as previously recommend and check the logic of what was being asked for
    2) Put the server behind an application aware firewall/IPS that would be able to look for SQL injection attacks (like calls to xp_cmdshell, create table, drop table, and a whole slew of other potential bad SQL statements).
    3) Even with the special characters having to be allowed, you could still restrict access to other ones, like ';' for example, that would make it a little more difficult to do SQL injection since you can't string the attack statements together.

    Another random idea I had that I am not sure of the feasibility of, you might check the input at the very beginning and convert and special characters to something else that wouldn't cause the injection. So for example,. say you see & in the string...have your program convert & to ' AND ' ... that way you are left with your search query as your program understands it and maybe not have to worry about a direct attack. Not sure about this one, but my tired/exhausted brain is saying this might work...
    There is only one constant, one universal, it is the only real truth: causality. Action. Reaction. Cause and effect...There is no escape from it, we are forever slaves to it. Our only hope, our only peace is to understand it, to understand the 'why'. 'Why' is what separates us from them, you from me. 'Why' is the only real social power, without it you are powerless.

    (Merovingian - Matrix Reloaded)

  5. #5
    Senior Member
    Join Date
    Sep 2001
    Posts
    1,027
    ok:

    1- DO NOT have the web app connect to the database using the sa account (if using mssql), or other priviledge account. Ideally, create a specific dabatase user that will have only the minimum required table accesses for the job.

    2- make sure you escape quoting characters before passing any input into an sql query.

    3- DO NOT pass the operators as si from the search query. Have the developpers parse them as if they were diffrent than those used in the sql query and build an sql query from the parsed elements. Don't forget to escape (ie: quote arround text and escape quotes from text) the search terms.


    Ammo
    Credit travels up, blame travels down -- The Boss

  6. #6
    hi i read all asked for your sql question

    you can try run directly on "stored procedures" and don't claim for sql injection for a long time.


    stored procedure like that

    -- =============================================
    -- Create procedure with OUTPUT Parameters
    -- =============================================
    -- creating the store procedure
    IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = N'<procedure_name, sysname, proc_test>'
    AND type = 'P')
    DROP PROCEDURE <procedure_name, sysname, proc_test>
    GO

    CREATE PROCEDURE <procedure_name, sysname, proc_test>
    <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
    <@param2, sysname, @p2> <datatype_for_param2, , int> OUTPUT
    AS
    SELECT @p2 = @p2 + @p1
    GO

    -- =============================================
    -- example to execute the store procedure
    -- =============================================
    DECLARE <@variable_for_output_parameter, sysname, @p2_output> <datatype_for_output_parameter, , int>
    EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <@variable_for_output_parameter, sysname, @p2_output> OUTPUT
    SELECT <@variable_for_output_parameter, sysname, @p2_output>
    GO



    and login acces like this:

    create procedure sp_addlinkedsrvlogin
    @rmtsrvname sysname,
    @useself varchar(8) = 'true',
    @locallogin sysname = NULL,
    @rmtuser sysname = NULL,
    @rmtpassword sysname = NULL
    as
    declare @srvid smallint,
    @status smallint,
    @localsid varbinary(85),
    @ret int

    -- DISALLOW USER TRANSACTION
    set implicit_transactions off
    if @@trancount > 0
    begin
    raiserror(15002,-1,-1,'sp_addlinkedsrvlogin')
    return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('securityadmin') = 1)
    begin
    raiserror(15247,-1,-1)
    return (1)
    end

    -- VALIDATE @useself PARAMETER --
    select @useself = lower(@useself)
    if @useself is null or @useself not in ('true','false')
    begin
    raiserror(15600,-1,-1,'sp_addlinkedsrvlogin')
    return 1
    end

    -- CHECK REMOTE SERVER NAME.
    select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
    if @srvid is null
    begin
    raiserror(15015,-1,-1,@rmtsrvname)
    return (1)
    end

    -- IF SPECIFIED CHECK LOCAL USER NAME
    if (@locallogin IS NOT NULL)
    begin
    select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
    if @localsid IS NULL
    begin
    -- ADD ROW FOR NT LOGIN IF NEEDED --
    execute @ret = sp_MSaddlogin_implicit_ntlogin @locallogin
    if (@ret = 0)
    select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
    if (@localsid IS NULL)
    begin
    raiserror(15067,-1,-1,@locallogin)
    return (1)
    end
    end
    end

    -- 64 IMPLIES sysxlogins::ishqoutmap is TRUE
    select @status = 64

    -- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser, and @rmtpassword
    if @useself = 'true'
    begin
    select @rmtuser = NULL
    select @rmtpassword = NULL
    select @status = @status | 128
    end

    BEGIN TRAN

    -- DELETE EXISTING MAPPING(s) FOR THIS @sid
    update master.dbo.sysxlogins set xstatus = xstatus & ~192
    where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
    AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
    if @@rowcount = 0
    delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
    AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)

    -- ATTEMPT TO TAG THIS ONTO EXISTING ROW --
    update master.dbo.sysxlogins
    set xstatus = (xstatus & ~192) | @status,
    xdate2 = getdate(),
    password = convert(varbinary(256), encrypt(@rmtpassword))
    where srvid = @srvid AND isrpcinmap = 1
    AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
    AND ((name IS NULL AND @rmtuser IS NULL) OR name = @rmtuser)

    -- IF NO ROW UPDATED, INSERT NEW ROW --
    if (@@rowcount = 0)
    insert master.dbo.sysxlogins values
    (@srvid, @localsid, @status, getdate(), getdate(), @rmtuser,
    convert(varbinary(256), encrypt(@rmtpassword)), 0, NULL)

    COMMIT TRAN

    -- RETURN SUCCESS
    return(0) -- sp_addlinkedsrvlogin

    GO

    both sp are integrated sql server, the general format is:

    CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
    [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

    [ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

    [ FOR REPLICATION ]

    AS sql_statement [ ...n ]




    Saludos

  7. #7
    Senior Member Opus00's Avatar
    Join Date
    May 2005
    Posts
    143
    When doing content checking, wouldn't it be easier to check for what you expect rather than what you don't expect. What I mean is, what is expected is finite, you know what should be in those fields, anything else should be rejected regardless of the content. this would cover the unexpected. I'm no expert by any means, this just seems logical. Am I headed in the wrong direction?
    There are two rules for success in life:
    Rule 1: Don't tell people everything you know.

  8. #8
    Senior Member
    Join Date
    Jan 2004
    Location
    Hawaii
    Posts
    350
    Opus: We don't know what will be in the fields. The fields are in a work order that both have varying fields depending on the form, and varying values depending on the field.

    To all else: the language is C# (pure .NET managed code).

    A_T
    Geek isn't just a four-letter word; it's a six-figure income.

  9. #9
    rebmeM roineS enilnOitnA steve.milner's Avatar
    Join Date
    Jul 2003
    Posts
    1,021
    Suppose the operation works a bit like this:

    Code:
    Get UserInput
    Prefix UserInput with 'SELECT * FROM THIS_TABLE WHERE '
    execute UserInput on SQL server
    Now suppose I enter as user input:
    Code:
    DATE_TIME>'TODAY';DROP TABLE THIS_TABLE
    The server will execute the select and then drop the THIS_TABLE table.

    So, you need to search for ; (if your termchar is ; ) as a minimum.

    You should also look out for queries that are going to kill the database by taking up all the CPU

    Steve
    IT, e-commerce, Retail, Programme & Project Management, EPoS, Supply Chain and Logistic Services. Yorkshire. http://www.bigi.uk.com

  10. #10
    Hi,

    SQL injection happens when an improperly validated data field can be used to inject SQL statement or exec commands into your dbms.

    The validation can fail either at the client end (i.e. done poorly or none) or at the server end (ditto).

    You can test your field to see if it is vulnerable to SQL injection by entering the following (e.g. for a login page) or their unicode/hex equivalents:

    Name: '

    Password: ' (usually shown as *)

    If the field is not being validated then it will throw a SQL database error statement. Validation should exclude all unexpected characters e.g. '@{}[]<>|\/?%&^' etc . IOW only alpanumeric characters should be permitted. A combination of Javascript at the client end and stored procedures at the other end should do this.

    The reason for validating at both ends is that it is possible to trap the GET or POST at the client side using a virtual proxy and alter the supposedly validated data stream before forwarding it to the server.

    The other thing that you should do is ensure that the 'exec' commands are not available to the query running from the public web page (i.e. it should have minimal rights - if it's for query only for example, it should not have write or delete privileges, and not be sa)

    The reason why SQL injection is important is that it can be used to enumerate the database and to alter its contents.

    e.g once you have found out that SQL injection is possible, you could for example enter the following

    Name:'or 1=1 -- {this is the format for Microsoft SQL and may not work on other platforms)
    Password: 'or 1=1 --
    If the SQL statement reads

    select * from usernametable where uname = Name and passw = Password

    This will translate as

    select * from usernametable where uname = Name or 1 = 1 -- ...rest of statement ignored

    Result, you will be logged in because you meet the condition of the statement.

    Or you could enumerate the database e.g.

    Name: 'group by uname --

    This will throw an error telling you the next field in the database e.g. passw

    Name: 'group by uname, passw --

    This will throw an error telling you the next field e.g. CreditCardNo

    Suddenly you have a database well worth breaking into...especially if you fancy some free jail time.

    You can also type the variables by using the command

    Name: 'Having ...

    This causes the database to throw an error telling you the type and size of the variable.

    Once you know this you can insert data into the table e.g

    Name:' Insert into name, passw('tenzenryu', 'tenzenryuismypassword2')

    and suddenly I am a legitimate user -well kind of .

    or you can delete records, drop the table etc and generally vandalise

    This isn't the only database security problem you can have (e.g. a common one is blank sa or default passwords) and I suggest getting training in secure database design and implementation as well as secure web and SQL coding.

    R
    No one can foresee the consequences of being clever.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •