0

I want to secure parameterized SQL query from SQL injections. I have gone through an article which described about avoiding SQL injections using PreparedStatement. link

According to the article it describes that preparedstament helps to pick Pre-Compiled Query from Cache and laceholders are replaced with user data at the final step and the query won't be compiled again. Therefore it will help to avoid SQL injection if someone provide sql query as a user input since the query will be compiled only once.

But what happens if someone gives string with special characters such as 'x'='x'. For example, when placeholders are replaced with user data like 'x'='x' in the final query like "SELECT * FROM users WHERE username = 'blah' AND password = 'foo' OR 'x'='x'". How does Java PreparedStatement deal with these type of special characters?

Can anybody please help me to understand this

Kepler
  • 349
  • 1
  • 7
  • 18
  • 2
    Prepared statements do not _have to_ deal with this, _because_ the query and the data are send separately to the server. – CBroe Jun 20 '17 at 10:30
  • How should the OR be added, if not by you? – baao Jun 20 '17 at 10:30
  • read this https://stackoverflow.com/questions/1582161/how-does-a-preparedstatement-avoid-or-prevent-sql-injection – YCF_L Jun 20 '17 at 10:36
  • 1
    @ YCF_L Read the question. Within the link I have added the blog post contained in the answer that you have pointed. I asked only the missing point I got to know – Kepler Jun 20 '17 at 10:38
  • @Anushka actually, the topic linked in the question does contain the answer to your question. – Shadow Jun 20 '17 at 10:42
  • @Shadow I want to make it clear, if we give an input like that, will it be executed as x=x which is true or just consider it as a meaningless string ? – Kepler Jun 20 '17 at 10:44
  • Maybe a little analogy can help here. Let us think of your task to pick someone up from the airport as an SQL statement. `Go to the airport and pick up X`. Now if you allow me to insert data into that directly, horrible person that I am, I would replace `X` with `Mary, and then kill yourself`. You search your memory and remember that you do know a Mary, so that’s fine, and since you are a person that follows orders to the letter (as are computers, or in this case the database), you perform the statement as instructed - oops, now you are dead, because of my “SQL injection”. [...] – CBroe Jun 20 '17 at 10:47
  • 1
    [...] What prepared statements do, is separate this into two parts. The first one is, `I need you to pick someone up from the airport` - and _who_ you are supposed to pick up, you get told _separately_. Which in this case naturally means you only shrug and go, “I don’t know anyone named `Mary, and then kill yourself`” - and that’s that, no harm done. Because you know that you get your instruction and the data (name) to perform it on separately, there is no potential here for you to confuse parts of the _data_ with parts of the _instruction_. – CBroe Jun 20 '17 at 10:47

1 Answers1

1

Basically, the database engine parses the SQL string with placeholders and remembers that only that part is to be executed.

Once it has done this, it will accept the data and replace the placeholders with the actual data.

This means that if someone somehow manages to get an SQL string into one of your variables to corrupt the SQL and make it do something else, the database engine will treat is as text that won't be executed.

Because of this, you could potentially input full SQL statements into a text field, and it will just get inserted as text instead of being parsed and executed by the database engine and so, your code is "safe" (there's always something you missed somewhere that can be a potential vulnerability)

This is also not exclusive to JAVA, it also works this way for PHP etc, it's just telling the database to handle queries in a different way than it would normally do.

Florian Humblot
  • 1,065
  • 12
  • 27