• Post Reply Bookmark Topic Watch Topic
  • New Topic

How to prevent a user from writing to the database in any form or manner (e.g. inserts, updates etc)  RSS feed

 
Jason Smit
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I would like to know the best possible manner from the application development side, what is the best way to prevent a user from entering commands to a JTextArea field which could either:
- Update a record or field one of the table/s
- Insert a record
- Drop a database
etc etc...

From an DBA perspective, I know that users can be granted read or write rights to certain schemas and tables etc. But what if you do not access to the database (SYSTEM rights),
how would you do this from the development side.

Suggestions anybody?
 
Jesper de Jong
Java Cowboy
Sheriff
Posts: 16060
88
Android IntelliJ IDE Java Scala Spring
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you should always check data that is entered by the user, and never put user input in an SQL statement directly, otherwise your program will be vulnerable to SQL injection.

Never create SQL statements in your code by concatenating strings together. Use a prepared statement instead and use statement parameters instead of concatenating values.
 
Jason Smit
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Thanks I have done research on those links.

The thing is this. My application is meant for users to run an SQL query to retrieve data from the database and display it to a JTable.

How would this then be possible to have prepare Statements?

I can see this working from the login side (username, and password etc etc). But how would this work if the user is writing the SQL code himself. Think of it like Toad, or SQL Developer or SQLTools.
 
Knute Snortum
Sheriff
Posts: 4279
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One way to minimize the user's ability to modify the DB is to scan the input string with regular expressions (regex). If you're not familiar with them, they are sort of a super matching search. You should read up on them, but because they are difficult to start with, I'll give you an example that will get you started. Here we're looking for "update".



You can add searches for other forbidden words. Also, search for semicolons (;).

Why regexs and not just indexOf()? Because SQL is case insensitive so the user could enter "UPDATE" or "update" or "Update" or "uPdAtE", etc.
 
fred rosenberger
lowercase baba
Bartender
Posts: 12563
49
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't most database systems give you the ability to restrict access by user ID? If each person has their own ID, or even if the application logs in under a single ID, couldn't you set it to a 'read only' kind of access? Then even if they manage to slip an 'update' into the SQL, the DB would still block it.
 
Stefan Evans
Bartender
Posts: 1837
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another option is to use the java.sql API you have been given.
Take a look at the Statement class - I'm pretty sure you can find a method that does exactly what you want.
 
Jason Smit
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:One way to minimize the user's ability to modify the DB is to scan the input string with regular expressions (regex). If you're not familiar with them, they are sort of a super matching search. You should read up on them, but because they are difficult to start with, I'll give you an example that will get you started. Here we're looking for "update".



You can add searches for other forbidden words. Also, search for semicolons (;).

Why regexs and not just indexOf()? Because SQL is case insensitive so the user could enter "UPDATE" or "update" or "Update" or "uPdAtE", etc.


Hi
Is it possible to breakdown what that regular expression is doing for the update string??? Each character and what it represents?

PS. do you have a document on regular expression or a good reference site for this?
 
Jason Smit
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
conn = DriverManager.getConnection(thinConn,username_,password_);

Is it possible for using Prepared Statements or should something else be in use to prevent SQL injection when user is attempting to log on?
 
Knute Snortum
Sheriff
Posts: 4279
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jason Smit wrote:
Knute Snortum wrote:One way to minimize the user's ability to modify the DB is to scan the input string with regular expressions (regex). If you're not familiar with them, they are sort of a super matching search. You should read up on them, but because they are difficult to start with, I'll give you an example that will get you started. Here we're looking for "update".



You can add searches for other forbidden words. Also, search for semicolons (;).

Why regexs and not just indexOf()? Because SQL is case insensitive so the user could enter "UPDATE" or "update" or "Update" or "uPdAtE", etc.


Hi
Is it possible to breakdown what that regular expression is doing for the update string??? Each character and what it represents?

PS. do you have a document on regular expression or a good reference site for this?


(?i) = do a case insensitive search
update = look for the literal "update"
.* = match the rest of the string

Note that "update" must be the first word, no white space. Perhaps a better regex that catches initial white spaces is:

"\\s*(?i)update.*"

For an interactive tutorial on regexes, try this.
 
Stefan Evans
Bartender
Posts: 1837
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess I am being too subtle.

The method you are after is statement.executeQuery()

It does exactly what it says it does - runs a query
It prevents users doing anything else - no DML, no inserts, updates or deletes

Why complicate things with regular expressions when you don't have to?

 
Knute Snortum
Sheriff
Posts: 4279
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It prevents users doing anything else - no DML, no inserts, updates or deletes


I was able to do an INSERT into my DB with executeQuery().
 
Tim Holloway
Saloon Keeper
Posts: 18797
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use executeQuery all the time to do table mods.

The way to totally prevent a user from writing to a database is to secure the user's database connection account. That is, the SQL GRANT rights. You can create a user ID with SELECT rights but no UPDATE, INSERT, or DELETE rights. Then lock down the more powerful accounts (change their passwords) to prevent unauthorized users from employing them. Java code alone cannot secure the database. Someone could always hack up a .Net app, use a database utiltity or whatever.

It gets a little stickier when you're dealing with a multi-user application such as a web application. That's because if you're using a connection pool, your pool user has to have greatest-common-denominator access rights. One way to get around that is to put the sensitve operations in a separate webapp that ordinary users cannot log into. Or, failing that, to employ an alternate connection with alternate user rights, though that's trickier to keep people from finding loopholes.

You cannot prevent Java code from attempting bad things. You can only limit what the database will accept. If a person is a Java programmer and their job requires them to code potentially dangerous operations and you cannot hand them a restricted account, then you should be able to trust them. Auditing the code helps, too, but if you cannot trust your programmers, then you have bigger issues than technical ones.
 
Stefan Evans
Bartender
Posts: 1837
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Really?
Are you sure you are not using the execute() method?
I just tried it with a local Derby database - an insert statement on a table threw the following exception:
Statement.executeQuery() cannot be called with a statement that returns a row count.

The API specificially states it throws an exception "if a database access error occurs, this method is called on a closed Statement, the given SQL statement produces anything other than a single ResultSet object, the method is called on a PreparedStatement or CallableStatement"


 
Knute Snortum
Sheriff
Posts: 4279
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But did the DB get updated? With mine and PostgreSQL, I get an error from the DB but the statement has already executed. I guess you could do a rollback. Here's my code:

 
Dave Tolls
Ranch Foreman
Posts: 3061
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, the joys of implementations that don't really follow the meaning of the contract.
Pretty sure it does what it's supposed to do in Oracle, for example, which is fail.
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DB2 doesn't fail, although I usually evaluate the return code and display a success/failure message to the user.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!