Safety question

[dennisGuru]

Hello,

I'm using tomcat 4.1 and mysql. In the beans I connect to the database with sql queries. Now i have heard that you do not end these queries with a ";". Because then the querie can be split up and someone can access the database and, for example, delete everything.

In my jsp pages i use forms. Can someone give me some clarity about this?

Thanks in advance.

 

[Smith5646]

I don't believe mySQL will allow this but here is what I understand the issue to be. SQL Server and a few others allow multiple commands on a single sql statement separated by a semicolon. For example, "select * from tableA; delete from tableB;

An example of the problem you are referring to is: If you post a form page requesting a login id and password. Your sql statement to validate may look like "select * from LoginTable where loginID = 'value from form' and password = 'value from form';.

If a malicious user could guess your table name (and who doesn't use logintable or usertable for their table name), he could enter a password like mypassword';delete from LoginTable;

Your sql statement would now be split into three parts based on the location of the semicolons:
1) select * from LoginTable where loginID = 'value from form' and password = 'mypassword';
2) delete from LoginTable;
3) '

The first two would execute successfully but the third one would not. However, the damage has already been done by statement 2.

One way I know to get around this is to put () around your conditions in your where clause. Instead of "select * from LoginTable where loginID = 'value from form' and password = 'value from form'; use "select * from LoginTable where (loginID = 'value from form') and (password = 'value from form');. Then if they did the malicious hacking, the statements would read:
1) select * from LoginTable where (loginID = 'value from form') and (password = 'mypassword';
2) delete from LoginTable;
3) ')
Because of the non-matching (), the sql parser will not be able to process the requests. When the first statement dies, it does not continue on to the next. Of course you may have to get tricky and vary the number of pairs of () you are using to keep them from figuring it out. One app I know uses a varying number of () so it is harder to hack. It can use anywhere between 1 and 5 sets...(loginID = 'value from form') to (((((loginID = 'value from form'))))) and it varies everytime the page is submitted. This makes it harder on the hacker because even if he guesses the table name, he has to try to hit a moving target with the number of () to use.

The other way to protect yourself is to use non-standard table names such as GuessMyPrefixToo_LoginTable instead of just LoginTable.