Java Code Examples for android.database.DatabaseUtils#sqlEscapeString()

private String sqliteEscape(String keyWord) {
        return DatabaseUtils.sqlEscapeString(keyWord);
//        if ("".equals(keyWord) || keyWord == null) {
//            return keyWord;
//        }
//        return keyWord
//                .replace("'", "''")
//                .replace("/", "//")
//                .replace("[", "/[")
//                .replace("]", "/]")
//                .replace("%", "/%")
//                .replace("&", "/&")
//                .replace("_", "/_")
//                .replace("(", "/(")
//                .replace(")", "/)")
//                ;
    }
 

/**
 * Prepare the where command and adds the global settings
 * @param where The where command
 * @return The new where command
 */
private String prepareWhere(String where) {
    // Ignore non-PlayMusic tracks
    where = combineWhere(where, "LocalCopyType != 300");

    // Loads only offline tracks
    if (mOfflineOnly)
        where = combineWhere(where, "LocalCopyPath IS NOT NULL");

    // Search only items which contains the key
    if (!TextUtils.isEmpty(mSearchKey)) {
        String searchKey = DatabaseUtils.sqlEscapeString("%" + mSearchKey + "%");

        where = combineWhere(where, "(" + COLUMN_ARTIST + " LIKE " + searchKey + ")");
    }

    return where;
}
 

@Override
public QiscusChatRoom getChatRoomWithUniqueId(String uniqueId) {
    String query = "SELECT * FROM "
            + QiscusDb.RoomTable.TABLE_NAME + " WHERE "
            + QiscusDb.RoomTable.COLUMN_UNIQUE_ID + " = " + DatabaseUtils.sqlEscapeString(uniqueId);

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);

    if (cursor.moveToNext()) {
        QiscusChatRoom qiscusChatRoom = QiscusDb.RoomTable.parseCursor(cursor);
        qiscusChatRoom.setMember(getRoomMembers(qiscusChatRoom.getId()));
        QiscusComment latestComment = getLatestComment(qiscusChatRoom.getId());
        if (latestComment != null) {
            qiscusChatRoom.setLastComment(latestComment);
        }
        cursor.close();
        return qiscusChatRoom;
    } else {
        cursor.close();
        return null;
    }
}
 

@Override
public void updateRoomMember(long roomId, QiscusRoomMember qiscusRoomMember, String distinctId) {
    distinctId = distinctId == null ? "default" : distinctId;
    String where = QiscusDb.RoomMemberTable.COLUMN_ROOM_ID + " = " + roomId + " AND "
            + QiscusDb.RoomMemberTable.COLUMN_USER_EMAIL + " = " + DatabaseUtils.sqlEscapeString(qiscusRoomMember.getEmail());

    sqLiteWriteDatabase.beginTransactionNonExclusive();
    try {
        sqLiteWriteDatabase.update(QiscusDb.RoomMemberTable.TABLE_NAME,
                QiscusDb.RoomMemberTable.toContentValues(roomId, distinctId, qiscusRoomMember), where, null);
        sqLiteWriteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        QiscusErrorLogger.print(e);
    } finally {
        sqLiteWriteDatabase.endTransaction();
    }

    addOrUpdate(qiscusRoomMember);
}
 

@Override
public QiscusComment getComment(String uniqueId) {
    String query = "SELECT * FROM "
            + QiscusDb.CommentTable.TABLE_NAME + " WHERE "
            + QiscusDb.CommentTable.COLUMN_UNIQUE_ID + " = " + DatabaseUtils.sqlEscapeString(uniqueId);

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);
    if (cursor.moveToNext()) {
        QiscusComment qiscusComment = QiscusDb.CommentTable.parseCursor(cursor);
        QiscusRoomMember qiscusRoomMember = getMember(qiscusComment.getSenderEmail());
        if (qiscusRoomMember != null) {
            qiscusComment.setSender(qiscusRoomMember.getUsername());
            qiscusComment.setSenderAvatar(qiscusRoomMember.getAvatar());
        }
        cursor.close();
        return qiscusComment;
    } else {
        cursor.close();
        return null;
    }
}
 

@Override
public QiscusRoomMember getMember(String email) {
    String query = "SELECT * FROM "
            + QiscusDb.MemberTable.TABLE_NAME + " WHERE "
            + QiscusDb.MemberTable.COLUMN_USER_EMAIL + " = " + DatabaseUtils.sqlEscapeString(email);

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);

    try {
        if (cursor != null && cursor.moveToNext()) {
            QiscusRoomMember qiscusRoomMember = QiscusDb.MemberTable.getMember(cursor);
            cursor.close();
            return qiscusRoomMember;
        } else {
            cursor.close();
            return null;
        }
    } catch (Exception e) {
        cursor.close();
        QiscusErrorLogger.print(e);
        return null;
    }
}
 

@Override
public void update(QiscusRoomMember qiscusRoomMember) {
    String where = QiscusDb.MemberTable.COLUMN_USER_EMAIL + " = " + DatabaseUtils.sqlEscapeString(qiscusRoomMember.getEmail());

    sqLiteWriteDatabase.beginTransactionNonExclusive();
    try {
        sqLiteWriteDatabase.update(QiscusDb.MemberTable.TABLE_NAME,
                QiscusDb.MemberTable.toContentValues(qiscusRoomMember), where, null);
        sqLiteWriteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        QiscusErrorLogger.print(e);
    } finally {
        sqLiteWriteDatabase.endTransaction();
    }
}
 

/**
 * Prepare the where command and adds the global settings
 * @param where The where command
 * @return The new where command
 */
private String prepareWhere(String where) {
    // Search only items which contains the key
    if (!TextUtils.isEmpty(mSearchKey)) {
        String searchKey = DatabaseUtils.sqlEscapeString("%" + mSearchKey + "%");

        where = combineWhere(where, "(" + COLUMN_NAME + " LIKE " + searchKey + ")");
    }

    return where;
}
 

/**
 * Prepare the where command and adds the global settings
 * @param where The where command
 * @return The new where command
 */
private String prepareWhere(String where) {
    // Ignore non-PlayMusic tracks
    where = combineWhere(where, "LocalCopyType != 300");

    // Loads only offline tracks
    if (mOfflineOnly)
        where = combineWhere(where, "LocalCopyPath IS NOT NULL");

    // Loads only positive rated tracks
    if (mRatedOnly)
        where = combineWhere(where, "Rating > 0");

    // Search only items which contains the key
    if (!TextUtils.isEmpty(mSearchKey)) {
        String searchKey = DatabaseUtils.sqlEscapeString("%" + mSearchKey + "%");

        String searchWhere = COLUMN_ALBUM + " LIKE " + searchKey;
        searchWhere += " OR " + COLUMN_TITLE + " LIKE " + searchKey;
        searchWhere += " OR " + COLUMN_ALBUM_ARTIST + " LIKE " + searchKey;
        searchWhere += " OR " + COLUMN_ARTIST + " LIKE " + searchKey;

        where = combineWhere(where, searchWhere);
    }

    return where;
}
 

@Override
public void onCreate(@NonNull SQLiteDatabase db) {
    String CREATE_BOOKMARK_TABLE = "CREATE TABLE " +
            DatabaseUtils.sqlEscapeString(TABLE_DOWNLOADS) + '(' +
            DatabaseUtils.sqlEscapeString(KEY_ID) + " INTEGER PRIMARY KEY," +
            DatabaseUtils.sqlEscapeString(KEY_URL) + " TEXT," +
            DatabaseUtils.sqlEscapeString(KEY_TITLE) + " TEXT," +
            DatabaseUtils.sqlEscapeString(KEY_SIZE) + " TEXT" + ')';
    db.execSQL(CREATE_BOOKMARK_TABLE);
}
 

@Override
public void delete(QiscusComment qiscusComment) {
    String where = QiscusDb.CommentTable.COLUMN_UNIQUE_ID + " = " + DatabaseUtils.sqlEscapeString(qiscusComment.getUniqueId());

    sqLiteWriteDatabase.beginTransactionNonExclusive();
    try {
        sqLiteWriteDatabase.delete(QiscusDb.CommentTable.TABLE_NAME, where, null);
        sqLiteWriteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        QiscusErrorLogger.print(e);
    } finally {
        sqLiteWriteDatabase.endTransaction();
    }
    deleteLocalPath(qiscusComment.getId());
}
 

@Override
public void update(QiscusComment qiscusComment) {
    String where = QiscusDb.CommentTable.COLUMN_UNIQUE_ID + " = " + DatabaseUtils.sqlEscapeString(qiscusComment.getUniqueId());

    sqLiteWriteDatabase.beginTransactionNonExclusive();
    try {
        sqLiteWriteDatabase.update(QiscusDb.CommentTable.TABLE_NAME, QiscusDb.CommentTable.toContentValues(qiscusComment), where, null);
        sqLiteWriteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        QiscusErrorLogger.print(e);
    } finally {
        sqLiteWriteDatabase.endTransaction();
    }
}
 

@Override
public boolean isContains(QiscusComment qiscusComment) {
    String query = "SELECT * FROM "
            + QiscusDb.CommentTable.TABLE_NAME + " WHERE "
            + QiscusDb.CommentTable.COLUMN_UNIQUE_ID + " = " + DatabaseUtils.sqlEscapeString(qiscusComment.getUniqueId());

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);
    boolean contains = cursor.getCount() > 0;
    cursor.close();
    return contains;
}
 

@Override
public boolean isContains(QiscusRoomMember qiscusRoomMember) {
    String query = "SELECT * FROM "
            + QiscusDb.MemberTable.TABLE_NAME + " WHERE "
            + QiscusDb.MemberTable.COLUMN_USER_EMAIL + " = " + DatabaseUtils.sqlEscapeString(qiscusRoomMember.getEmail());

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);
    boolean contains = cursor.getCount() > 0;
    cursor.close();
    return contains;
}
 

@Override
public void deleteRoomMember(long roomId, String email) {
    String where = QiscusDb.RoomMemberTable.COLUMN_ROOM_ID + " = " + roomId
            + " AND " + QiscusDb.RoomMemberTable.COLUMN_USER_EMAIL + " = " + DatabaseUtils.sqlEscapeString(email);

    sqLiteWriteDatabase.beginTransactionNonExclusive();
    try {
        sqLiteWriteDatabase.delete(QiscusDb.RoomMemberTable.TABLE_NAME, where, null);
        sqLiteWriteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        QiscusErrorLogger.print(e);
    } finally {
        sqLiteWriteDatabase.endTransaction();
    }
}
 

@Override
public boolean isContainsRoomMember(long roomId, String email) {
    String query = "SELECT * FROM "
            + QiscusDb.RoomMemberTable.TABLE_NAME + " WHERE "
            + QiscusDb.RoomMemberTable.COLUMN_ROOM_ID + " = " + roomId
            + " AND " + QiscusDb.RoomMemberTable.COLUMN_USER_EMAIL
            + " = " + DatabaseUtils.sqlEscapeString(email);

    Cursor cursor = sqLiteReadDatabase.rawQuery(query, null);
    boolean contains = cursor.getCount() > 0;
    cursor.close();
    return contains;
}
 

@Override
public void onCreate(@NonNull SQLiteDatabase db) {
    String CREATE_BOOKMARK_TABLE = "CREATE TABLE " +
        DatabaseUtils.sqlEscapeString(TABLE_BOOKMARK) + '(' +
        DatabaseUtils.sqlEscapeString(KEY_ID) + " INTEGER PRIMARY KEY," +
        DatabaseUtils.sqlEscapeString(KEY_URL) + " TEXT," +
        DatabaseUtils.sqlEscapeString(KEY_TITLE) + " TEXT," +
        DatabaseUtils.sqlEscapeString(KEY_FOLDER) + " TEXT," +
        DatabaseUtils.sqlEscapeString(KEY_POSITION) + " INTEGER" + ')';
    db.execSQL(CREATE_BOOKMARK_TABLE);
}
 

@Override
public void onCreate(@NonNull SQLiteDatabase db) {
    String CREATE_BOOKMARK_TABLE = "CREATE TABLE " +
            DatabaseUtils.sqlEscapeString(TABLE_DOWNLOADS) + '(' +
            DatabaseUtils.sqlEscapeString(KEY_ID) + " INTEGER PRIMARY KEY," +
            DatabaseUtils.sqlEscapeString(KEY_URL) + " TEXT," +
            DatabaseUtils.sqlEscapeString(KEY_TITLE) + " TEXT," +
            DatabaseUtils.sqlEscapeString(KEY_SIZE) + " TEXT" + ')';
    db.execSQL(CREATE_BOOKMARK_TABLE);
}
 

/**
 * Converts a value input into a String representation of that.
 * <p>
 * If it has a {@link TypeConverter}, it first will convert it's value into its {@link TypeConverter#getDBValue(Object)}.
 * <p>
 * If the value is a {@link Number}, we return a string rep of that.
 * <p>
 * If the value is a {@link BaseModelQueriable} and appendInnerQueryParenthesis is true,
 * we return the query wrapped in "()"
 * <p>
 * If the value is a {@link NameAlias}, we return the {@link NameAlias#getQuery()}
 * <p>
 * If the value is a {@link SQLOperator}, we {@link SQLOperator#appendConditionToQuery(QueryBuilder)}.
 * <p>
 * If the value is a {@link Query}, we simply call {@link Query#getQuery()}.
 * <p>
 * If the value if a {@link Blob} or byte[]
 *
 * @param value                       The value of the column in Model format.
 * @param appendInnerQueryParenthesis if its a {@link BaseModelQueriable} and an inner query value
 *                                    in a condition, we append parenthesis to the query.
 * @return Returns the result as a string that's safe for SQLite.
 */
@SuppressWarnings("unchecked")
@Nullable
public static String convertValueToString(@Nullable Object value,
                                          boolean appendInnerQueryParenthesis,
                                          boolean typeConvert) {
    if (value == null) {
        return "NULL";
    } else {
        String stringVal;
        if (typeConvert) {
            TypeConverter typeConverter = FlowManager.getTypeConverterForClass(value.getClass());
            if (typeConverter != null) {
                value = typeConverter.getDBValue(value);
            }
        }

        if (value instanceof Number) {
            stringVal = String.valueOf(value);
        } else if (value instanceof Enum) {
            stringVal = DatabaseUtils.sqlEscapeString(((Enum) value).name());
        } else {
            if (appendInnerQueryParenthesis && value instanceof BaseModelQueriable) {
                stringVal = String.format("(%1s)", ((BaseModelQueriable) value).getQuery().trim());
            } else if (value instanceof NameAlias) {
                stringVal = ((NameAlias) value).getQuery();
            } else if (value instanceof SQLOperator) {
                QueryBuilder queryBuilder = new QueryBuilder();
                ((SQLOperator) value).appendConditionToQuery(queryBuilder);
                stringVal = queryBuilder.toString();
            } else if (value instanceof Query) {
                stringVal = ((Query) value).getQuery();
            } else if (value instanceof Blob || value instanceof byte[]) {
                byte[] bytes;
                if (value instanceof Blob) {
                    bytes = ((Blob) value).getBlob();
                } else {
                    bytes = ((byte[]) value);
                }
                stringVal = "X" + DatabaseUtils.sqlEscapeString(SqlUtils.byteArrayToHexString(bytes));
            } else {
                stringVal = String.valueOf(value);
                if (!stringVal.equals(Operator.Operation.EMPTY_PARAM)) {
                    stringVal = DatabaseUtils.sqlEscapeString(stringVal);
                }
            }
        }

        return stringVal;
    }
}
 

@Override
public Cursor runQueryOnBackgroundThread(CharSequence constraint) {

  String where = null;
  android.net.Uri db = null;
  StringBuilder s = new StringBuilder();

  if (constraint != null) {
    String filter = DatabaseUtils.sqlEscapeString(constraint.toString() + '%');

    if (SdkLevel.getLevel() >= SdkLevel.LEVEL_HONEYCOMB_MR1) {
      db = HoneycombMR1Util.getDataContentUri();
      s.append("(" + HoneycombMR1Util.getDataMimeType() + "='" + HoneycombMR1Util.getEmailType() + "')");
      s.append(" AND ");
      s.append("(display_name LIKE ");
      s.append(filter);
      s.append(")");
    } else {
      db = ContactMethods.CONTENT_EMAIL_URI;
      s.append("(name LIKE ");
      s.append(filter);
      s.append(") OR (display_name LIKE ");
      s.append(filter);
      s.append(")");
    }
  }
  where = s.toString();

  // Note(hal): This lists the column names in the table being accessed, since they aren't
  // obvious to me from the documentation
  if (DEBUG) {
    Cursor c = context.getContentResolver().query(db, null, null, null, null, null);
    Log.d(TAG, "listing columns");
    for (int i = 0; i<c.getColumnCount(); i++) {
      Log.d(TAG, "column " + i + "=" + c.getColumnName(i));
    }
  }

  if (SdkLevel.getLevel() >= SdkLevel.LEVEL_HONEYCOMB_MR1) {
    return contentResolver.query(db, POST_HONEYCOMB_PROJECTION,
        where, null, SORT_ORDER);
  } else {
    return contentResolver.query(db, PRE_HONEYCOMB_PROJECTION,
        where, null, SORT_ORDER);
  }
}