Code which is free from problems of SQL Injection

Question

I have a comments box in my website

<td id="commentsBox" class="xec" size="200"></td>

I use Javascript to read the comments box and create an XML string.

<ROWS><COMMENTS>My comments</COMMENTS></ROWS>

The XML string is passed to a stored procedure via java. (I have simplified the SQL Code and XML String for the purposes of the question)

CREATE PROCEDURE [DB].[TEST$ExecuteXML] @doc VARCHAR(max)
    ,@P_Result VARCHAR(max) OUTPUT
AS
BEGIN
    DECLARE @idoc INT;

    EXEC sp_xml_preparedocument @idoc OUTPUT
        ,@doc;



        INSERT INTO MyTable (
            COMMENTS
            ,UPDATE_DATE
            )
        SELECT COMMENTS
            ,getDate()
        FROM OPENXML(@idoc, '/ROWS', 1) WITH (
                COMMENTS VARCHAR(200) 'COMMENTS'
                );


    SET @p_result = 1;
END

I have looked at sites dealing with SQL Injection such as https://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx

Is it possible to enter something into the textbox that will be destructive to the database?

UPDATE

In response to @Linky I add here (part of) the Java code - although I am at a loss to understand how this could be problematic, as the premise of my question is that the SQL Server procedure could accept anything in the XML.

XMLObject br = new XMLObject(xmlString);
String result = br.update();

public class XMLObject {

    public static final int RESULT_FAILED = 0;
    public static final int RESULT_SUCCESS = 1;

    protected DBConnection dbConn = null;

    protected String theXML=null;

    public XMLObject(String theXML) {       
        this.theXML=theXML;
    }


    public String update() {
        String result;
        ArrayList<DBField> fields = new ArrayList<>();
        fields.add(new DBField(DBField.STRING, theXML, false));
        result = DMLUtils.executeString("ExecuteXML", fields);
        return result;
    }

}

Usually the problem is in the code that calls the db command (either procedure or any other DML). Yes - it's still possible - it depends on how you call the Procedure from Java — Linky, Sep 17 '15 at 16:58
To understand how it could be problematic see http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Linky, Sep 17 '15 at 18:10

score 2 · Answer 1 · edited Mar 17 '17 at 10:45

2

The best way to avoid SQL Injection attacks is to tie the content to a particular column. Directly entering the users comments into the database opens you up to cross site scripting attacks if that data is ever displayed to the user. I would suggest that you take this question to the Security Stack Exchange.

edited Mar 17 '17 at 10:45

Community

1
1

answered Sep 17 '15 at 16:41

ojblass

21,146
22
83
132

score 1 · Accepted Answer · answered Sep 17 '15 at 16:47

1

Injection comes from allowing SQL data to become or be treated as SQL commands. In your example you are keeping data and commands clearly delineated, and AFAIK, neither OPENXML nor sp_xml_preparedocumen can on their own be used cause injection.

So, in my professional opinion, this appears to be safe from injection.

answered Sep 17 '15 at 16:47

RBarryYoung

55,398
14
96
137

1

You're right that the code shown seems to be safe - but only the shown code. IMHO it's still possible to be open to sql injection as the java-sql bridge is not shown – Linky Sep 17 '15 at 17:02
@Linky That's true of anything and would make the question "*Is this code safe*" always moot. But it isn't moot, and it *is* important to be able to identify which code is safe and which is not. This code, as presented, is safe. – RBarryYoung Sep 17 '15 at 19:11

Code which is free from problems of SQL Injection

2 Answers2