0

I recently learned not using the .set() methods of PreparedStatements is a pretty huge security flaw.

After looking at some code examples I have a few questions regarding PreparedStatements and their security.

If someone can attack a MySQL query to change the data, how exactly does the PreparedStatement's .set() methods prevent an attack?

For example, if the query is

INSERT INTO table_name(?, ?);

(Assuming both of the question marks will be strings)

Couldn't someone simply change the table_name to another table?

Another example; If the query is

 UPDATE table_name SET column_name=?;

Couldn't a person change the column_name to make the query change a differnet column?

Final example I could think of; If the query is

SELECT column_name FROM table_name WHERE column2_name = ?;

Would it be possible for an attacker to modify the column_name, table_name, etc. Or inject other actions into the query?

As you can probably tell, I don't know much about security and MySQL, so excuse me if these are extremely obvious questions. Thank you!

C_Neth
  • 696
  • 2
  • 9
  • 23

2 Answers2

1

The Prepared Statement, if used properly, does protect against SQL injection. But remember, Prepared statements can protect only data, but can't defend the program itself.

Look here: How can prepared statements protect from SQL injection attacks?

Community
  • 1
  • 1
m.aibin
  • 3,528
  • 4
  • 28
  • 47
1

The value in prepared statements is that you separate your variable data from the query string itself. This provides two main advantages:

  1. in the case of a repeated query which differs only in the data sent, the query itself does not need to be re-parsed; instead, only the new data needs to be sent.

  2. You are protected from SQL injection attacks that might cause improperly escaped data included directly in your query string, because your data is separate from the query string itself and is sent in a safe manner.

Prepared statements do not (and are not intended to) protect you from man-in-the-middle attacks between your client and your database that might alter the query (or data) you intended to send. For that, you need to use an SSL transport, and otherwise ensure that attackers can't interpose themselves in your communication with your database.

Erwin Bolwidt
  • 30,799
  • 15
  • 56
  • 79
jbafford
  • 5,528
  • 1
  • 24
  • 37
  • Alright. So the gist of it is that the SQL injection attacks can only modify the data sent within the query, not the query itself? And that MITM attacks are actually able to modify the query? (Thank you for the reply btw). – C_Neth Jan 30 '16 at 07:18
  • 1
    A successful SQL injection attack would imply your data is part of the query string itself, and the vulnerability is that a query does something you didn't intend, beyond just incorrect data going into the db. If the data is separated from the query, then any malicious input is just part of the data. A MITM could potentially affect anything your application passes through the attacker, provided the attacker is able to read and modify the data. (But note that a read-only MITM could still be dangerous.) – jbafford Jan 30 '16 at 07:27