SQL Exception (unrecognized token) when string contains quotes

Question

I'm getting SQL Exception (unrecognized token) when I use my function to retrieve data from "mysql.db". The exception occurs when String address has quotes inside.

I already tried to change this line

KEY_ADDRESS + "='" + address + "'",

to

KEY_ADDRESS + "=\"" + address + "\"",

It solves my problem for strings containing single quote (') and (`), but creates another problem for strings containing double quotes (").

I tried using

DatabaseUtils.sqlEscapeString(address);

with no effect.

I tried to use escape function from this question: How do I escape special characters in MySQL?, but it did not work.

This is my code:

public Cursor getNameFromAddress(String address) throws SQLException
{
    Cursor mCursor =
            db.query(DATABASE_TABLE_PRINC, new String[] {
                    KEY_ROWID,
                    KEY_NAME,
                    KEY_ADDRESS
                    },
                    KEY_ADDRESS + "='" + address + "'",
                    null,
                    null,
                    null,
                    null,
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

`sqlEscapeString` seems like the right way to do this. What do you mean "no effect"? What does that method return? — tadman, Oct 24 '12 at 14:48
I'm sorry, when I use sqlEscapeString the returned result is 'uvuwub}f84xuy}bap}zu84uvw84"\`w' and the SQLiteException says: near "uvuwub": syntax error: , while compiling: SELECT _id, name, address FROM sqltable WHERE address="uvuwub}f84xuy}bap}zu84uvw84"`w" — Daniel, Oct 24 '12 at 15:00

score 5 · Accepted Answer · answered Oct 24 '12 at 15:02

Those selectionArgs / whereArgs are intended to be used for that:

Cursor mCursor =
        db.query(DATABASE_TABLE_PRINC, new String[] {
                KEY_ROWID,
                KEY_NAME,
                KEY_ADDRESS
                },
                KEY_ADDRESS + "=?",
                new String[] { address },
                null,
                null,
                null,
                null);

replace all "='" + stuff + "'" with "=?" and put the data to fill the ?s in the order they have to be filled in a String[]

? will then automatically be replaced by escaped String data.

score 2 · Answer 2 · answered Oct 24 '12 at 15:01

missing "?" symbol

Cursor mCursor =
        db.query(DATABASE_TABLE_PRINC, new String[] {
                KEY_ROWID,
                KEY_NAME,
                KEY_ADDRESS
                },
                KEY_ADDRESS + "=?" ,
                new String[]{address},
                null,
                null,
                null,
                null);

score 2 · Answer 3 · answered Oct 24 '12 at 15:04

2

queryText = "select * from yourtable where address = ?";
mDb.rawQuery(queryText, new String[] {addressValue});

This way you solve your problem and also defend your system against SQL Injection.

answered Oct 24 '12 at 15:04

Lajos Arpad

64,414
37
100
175

SQL Exception (unrecognized token) when string contains quotes

3 Answers3