Prevent SQL injection with the new Api ?

[Haniman]

Hello,

in the olde version was an escapeQuotes function.

Is there something similar in the new API? Could find one so far.


thx

[Haniman]

i just wrote my own

[jpardoe]

Care to share?

[bart4president.com]

Yes I am quite interested in this as well!

[ext0sus]

Using PreparedStatements would probably be the best way to do it but I can't find a way to integrate it with DBManager. E.g:

Code: Select all

PreparedStatement sql = dbManager.getConnection().prepareStatement("SELECT password FROM accounts WHERE username=?");
sql.setString(1, name);

But there's no way of getting the result into an SFSArray as far as I know.

As for writing your own: I tried searching to see if I could find an example. Every article I read said "use a PreparedStatement". But I assume you would just want to search the string for any single quotes (') and put a backslash (\) or another single quote infront of them.

Is it possible that SFS does it behind the scenes? I tried injecting SQL into my own application's login and I just got an error.

[Lapo]

The use of PreparedStatement is indeed a good solution. I am adding a note in our todo list to better support them in the current API.

Thanks

[Haniman]

I just did an

public static String escapeQuotes(String inString ){
return inString.replaceAll("'", "''");
}

This forms all quotes in doublequotes, which makes them appear as an empty string.

This only avoids the simpliest injections. The only thing that really helps are these prepared statements. But i didnt tried them yet.

Just too much to do to get my game running on the new server.
I am near to cancel the portation ^^

[jpardoe]

Stick with it Haniman! You are always learning new things which will only help you in the future!

Can someone post a working code example of prepared statement use?

[Lapo]

UPDATE:
I need to correct myself on this:

The use of PreparedStatement is indeed a good solution. I am adding a note in our todo list to better support them in the current API.

You can already use PreparedStatement quite easily:

Code: Select all

Connection conn = getParentZone().getDBManager().getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM people WHERE age > ?");
stmt.setInt(1, 35);

// Obtain ResultSet
ResultSet res = stmt.executeQuery();

// Obtain SFSArray
ISFSArray arr = SFSArray.newFromResultSet(res);

Cheers

[jpardoe]

Great, thanks!

[Democre]

Also, remember to close the connections when done using them.

[jpardoe]

How?

[Lapo]

connection.close()

[jpardoe]

So simple!