How can I detect the presence of SQL code, in a page of android code?

Question

The purpose of this question, is not to figure out how to prevent SQL Injection attempts.

Instead I would like to know, how to detect lines of SQL code within an (Android Class) file

Unlike other SQL parser threads I am wanting to work within Android and I am simply after something like a Regex statement which could be used to detect SQL code within the current line

I have an example page of Android code like the below :

String query = "select * from users_table where username = '" +  u_username + "' and password = '" + u_password +"'";
SQLiteDatabase db
//some un-important code here...
Cursor c = db.rawQuery( p_query, null );
return c.getCount() != 0;
}

Within which there are a lines of SQL code like:

select * from users_table where username = '" +  u_username + "' and password = '" + u_password +"'

I have a function like the below, which reads in an (Android Class) file line by line. Then returns an ArrayList of String.

public ArrayList<String> SQLStatementsfromFile(File fileLocation) throws IOException {
    ArrayList<String> SQLStatements = new ArrayList<>();
    FileInputStream is;
    BufferedReader reader;
    if (fileLocation.exists()) {
        is = new FileInputStream(fileLocation);
        reader = new BufferedReader(new InputStreamReader(is));
        String line = reader.readLine();
        while (line != null) {
            line = reader.readLine();

            if (line != null) {
                if (!line.isEmpty()) {

                    if (line.contains(SQL))
                    SQLStatements.add(line);

                }
            }
        }
    }

    return SQLStatements;
}

What I would like to know is: are there any sort of possible Regex statements (or other code detection methods) which could be used to detect SQL code, from each line of android code (so it can be added to the ArrayList SQLStatements)?

To be clear, PLEASE do not give me tips on example code I have written above.

Or advice on examples given. Just PLEASE instead attempt to actually answer my question.

I know parameter-ised statements within SQL etc. are much more secure, but this is NOT why I have opened this thread, the above are simply examples of SQL code I would like to be detect (NOT SQL code I plan to utilize).

Thank you :-)

Do not use Regex or any other technique to determine presence of SQL code in the input, **instead use parameterized queries**. — Habib, Jun 23 '15 at 15:00
possible duplicate of [SQL parser library for Java](http://stackoverflow.com/questions/660609/sql-parser-library-for-java) — Stavr00, Jun 23 '15 at 15:18

melwil · Accepted Answer · 2015-06-23T16:07:22.910

0

Assuming any valid SQL statement has to start with INSERT INTO, UPDATE, SELECT or DELETEand end with a semicolon ;, you can find sql using the following:

/(?:INSERT INTO|UPDATE|SELECT|DELETE)(?:[^;'"]|(?:'[^']*?')|(?:"[^"]*?"))+;/i

So first off, the sql you wrote is not what I consider valid, as it lacks the ending semicolon.

If you can ensure the synax of sql follows the rules I stated earlier, the ; to end Java code lines shouldn't interfere with this regex.

Without the semicolon, your example code does not match, but insert the semicolon and it does. Example here:

https://regex101.com/r/zB2yJ1/1

edited Jun 23 '15 at 16:07

answered Jun 23 '15 at 15:56

melwil

2,547
1
19
34

SQL does not require a semicolon; it's used only for *separating* statements. – CL. Jun 23 '15 at 17:33
It's part of the ANSI SQL-92 standard, but some flavors of SQL may not require them. Why on earth you would write SQL without a terminator, I do not know. Even the flavors that allow you to use SQL without one, **may** fail without one in certain cases. Anyway, I posted the regex under the assumption that you do have a semicolon, simply removing it from the regex works just fine. – melwil Jun 23 '15 at 17:49
The syntax rules for `` indeed require a semicolon, but you cannot rely on any random Java code to actually use it. – CL. Jun 23 '15 at 18:02
Sure, and then you have an unreliable pattern. It works without it, as I said, but it's far less certain you will actually find SQL. – melwil Jun 23 '15 at 18:07

How can I detect the presence of SQL code, in a page of android code?

1 Answers1