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?

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 :-)

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.

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.

@frob, @deflinek: The poster (@menyo) already mentioned that they're using Client -> Server -> Database as the topology, so I think they're fine there.

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.

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.
...