Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

jdbc , sql injection & mysql keywords!

 
Mandar Khire
Ranch Hand
Posts: 569
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I write java program with mysql database. If in mysql database is not exist then program's function creates it by getting values from text-fields.
Here i write function for create database which can holds tables in future.

Problem is when i enter database name equal to 'mysql keywords' like add, as, by.....
I get com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

By reading various forums i get little bit idea about sql injection so i tried preparestatement also.
I tried as below:-

But i got same exception.
Can any one help me how i can avoid exception if from string databasename getting values as mysql keywords...there are almost 230 keywords.
I found various examples to avoid exception if database exist or table exist but at time of database creation example i can not find.
Till someone give me clue i will trying...if i found way then i will post here...Hope this will be easy...
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Generally only constant literals can be parametrized (replaced by a question mark in PreparedStatement). Table names, column names and, as it seems, database names (among other things such as keywords) cannot be parametrized, for various reasons. One of the reasons probably is that this could make the PreparedStatement vulnerable to some forms of SQL injection.

I don't use MySQL, but it appears that the correct way would be to enclose the database name in back ticks (`)

However, it is probably not a good idea to have a database name that is also a reserved word. Why would you want to do it? If the database name comes from a user, a possible workaround could be to use a constant prefix that would ensure that the resulting database name is not a reserved word.
 
Mandar Khire
Ranch Hand
Posts: 569
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin Vajsar,
I try as below with back tick `.

but now i got new java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). At line

When i change it to 0 then also i got exception...
What should be parameter enter in this type of code?
Sorry for asking basic questions...
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, you cannot parametrize the database name in any way. You must put in the database name, and if it is a reserved word, enclosing it in reverse apostrophes might help (I don't know this for sure, I don't use MySQL).

Question marks can be used only instead of literals - string, numeric or date/time constants. And when they are used instead of strings, you don't enclose them in quotes. A question mark is enclosed in quotes does not represent a parameter in PreparedStatement. This is why you got the error - the statement actually had no parameters at all.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic