Solution 1 :
c = db.rawQuery("SELECT clue FROM clue_table where player_name = '" +
player_name +"'", new String[]{});
// see here ^^^^^^^^^^^^^^
Ok, so that empty String[]
array you’re passing to the call is exactly the place parameters are supposed to go. So, the query should look like that:
c = db.rawQuery("SELECT clue FROM clue_table where player_name = ?", new String[]{player_name});
Just put a question mark where you want the parameter inserted. They will be inserted in order, the array can have more of these.
The driver will take care of escaping any apostrophes for you. If you don’t escape user input in any way, you risk a lot. Consider player name coming from user input in the app, and they write into the search box on a whim:
' delete from users; --
If you just paste it into your query it now looks like this:
SELECT clue FROM clue_table where player_name = ''; delete from users; --'
And you get your data dropped as a free bonus to search function.
Problem :
In the app I am building, I need to access an SQLite database run a query in which I have a name in the “where” clause. This name is randomly generated (from another query) and is assigned to a global variable. This variable then get’s passed to another query (in another method) where it’s used. Something a little like this:
public class DatabaseAccess {
public static String player_name;
Cursor c = null;
public String getPlayerName(String string) {
c = db.rawQuery("select player_name FROM name_table where level = 1 ORDER BY RANDOM() LIMIT 1", new String[]{});
StringBuffer buffer = new StringBuffer();
while (c.moveToNext()) {
String diff = c.getString(0);
buffer.append("" + diff);
}
player_name = buffer.toString();
return buffer.toString();
}
public String getClue(String string) {
c = db.rawQuery("SELECT clue FROM clue_table where player_name = '" + player_name +"'", new String[]{});
StringBuffer buffer = new StringBuffer();
while (c.moveToNext()) {
String diff = c.getString(0);
buffer.append("" + diff);
}
return buffer.toString();
}
The issue is that some of the names in the database have a '
symbol in them (for example Dara O’Briain).
When a name like this comes up, my code crashes with the error message:
Caused by: android.database.sqlite.SQLiteException: near "Briain": syntax error (code 1): , while compiling: select alt_name FROM player_names where player_name = 'Dara O'Briain'
It looks like it see’s the '
as a means to end the string and this causes a faulty query.
How can I account for these instances in my code?
Comments
Comment posted by stackoverflow.com/questions/41373232/…
Use parameters to solve this. Also solves your injection vulnerability. Not the same question, but same answers as: