Sign in to follow this  

Prepared statements and SQL injection.

This topic is 814 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a Client -> Server -> database structure. And I have a lot of prepared statements. I am somewhat familiar with SQL Injection and usually I sanitize all user input. But I read this from W3schools:

 

  • Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

 

Up till now I prepare all my statements without user input. like so:

String query = "SELECT * FROM dbTable WHERE id = ?";
stmt = conn.prepareStatement(query);

 

And then I set user input like below. "id" in this case is taken from a local file on the client that he might alter. It could represent raw user input as well.

stmt.setInt(1, id);

Can I now conclude that this statement is protected from injection? Even when id contains something like "1 OR 0 = 0" or any other forms of injection?

Edited by menyo

Share this post


Link to post
Share on other sites

That falls under the broad umbrella "Never trust the users".

 

Validate everything they do.  Don't trust any value they give you, and assume it could be malicious. Anything that comes from the user, even email addresses and passwords, should be treated with extreme care. 

 

As a best practice, it is most common for clients/users to send events and requests to the server.  The server validates the request but doesn't use the data directly; the request isn't passed directly into databases, instead it triggers actions in code that do things to server-side data.  

Share this post


Link to post
Share on other sites


And then I set user input like below. "id" in this case is taken from a local file on the client that he might alter. It could represent raw user input as well.
stmt.setInt(1, id);
Can I now conclude that this statement is protected from injection? Even when id contains something like "1 OR 0 = 0" or any other forms of injection?

If your code executes on server then you are safe. If your SQL statements are prepared and executed on client against one central DB then SQL injection is the least of your problems as you have already your DB compromised.

 

Rephrasing what Frob said, the client should only send the Id to the server, then on server you validate it (for example if that client is allowed to user that particular Id), set parameters and execute queries.

Share this post


Link to post
Share on other sites

Yes, assuming your prepared statement library doesn't have bugs, you can now assume that you don't have SQL injection vulnerabilities in that code.

One down! Only 9999 other kinds of vulnerabilities to go :-)

 

What could some of the other vulnerabilities be? I'm not expecting you to name all 9999 but just view common ones. I thought I should be decently save when I'm running the DB behind the server so no one can access the database since it blocks everything except the local connection. I'm very curious how there would be 9999 other vulnerabilities.

 

 

 


And then I set user input like below. "id" in this case is taken from a local file on the client that he might alter. It could represent raw user input as well.
stmt.setInt(1, id);
Can I now conclude that this statement is protected from injection? Even when id contains something like "1 OR 0 = 0" or any other forms of injection?

If your code executes on server then you are safe. If your SQL statements are prepared and executed on client against one central DB then SQL injection is the least of your problems as you have already your DB compromised.

 

Rephrasing what Frob said, the client should only send the Id to the server, then on server you validate it (for example if that client is allowed to user that particular Id), set parameters and execute queries.

 

 

Yeah, the DB is behind the server in architecture. And the DB only accepts queries coming from localhost.

 

 

That falls under the broad umbrella "Never trust the users".

 

Validate everything they do.  Don't trust any value they give you, and assume it could be malicious. Anything that comes from the user, even email addresses and passwords, should be treated with extreme care. 

 

As a best practice, it is most common for clients/users to send events and requests to the server.  The server validates the request but doesn't use the data directly; the request isn't passed directly into databases, instead it triggers actions in code that do things to server-side data.  

 

How can user input be malicious in any other way then SQL injection? If we disregard the client where the user only has a textfield to work with it still has to send something in the exact same packet the server is listening for.

 

Let's assume the client want a list of his owned items. I have a class that gets send over to the server like this:

 

class RequestPacket

{

    public boolean ownedItemsRequest;

    public boolean characterRequest;

    public boolean opponentRequest;

}

 

When my server receives something it will first check if the packet is an instance of ItemPacket. If it does not, and does not belong to any other class I registered it will only log this. If it does I know what kind of request has been made. In our case ownedItemsRequest would be true, so I run some code and use the prepared statement to get the items belonging to the client that asked for it. I then sent back that list of items to the client. Now I build up a nice UI for the player to see his items.

 

I do the same for dynamic input. That will also be sent in a class like above but with a String. Since I have a prepared statement I do not need to check the contents, or do I? But I probably will strip it down to numbers, upper/lower case and a couple of special symbols. Same goes for an email address, it makes no sense to not check for validness.

Share this post


Link to post
Share on other sites
The kinds of vulnerabilities that online games and web sites run into are very, very broad:

If you let players trade, a player may send a "I'm trading my rusty rock for his +11 Flaming Swort of Fire" message. Design the protocol to avoid this.
If you receive strings anywhere, a player may send a very long string that overflows whatever buffer you're using.
If you use a wireless keyboard for development/administration, someone may sniff your username/password from far away.
If you store passwords, you may be using a weak password hash function that is susceptible to reverse construction or rainbow tables.
If you allow account resets, you may be allowing users to "I forgot my email" and "I forgot my password" at the same time, allowing someone to take over an account.
If you allow user chat, users may pose as developer staff and ask other users for their passwords.
If you do not aggressively validate sessions for each packet/request, someone may start sending game packets without necessarily going through the intended login sequence.
...

Share this post


Link to post
Share on other sites

This topic is 814 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this