Win a copy of Grokking Bitcoin this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
Bartenders:
  • Carey Brown
  • salvin francis
  • Claude Moore

SQL injection query for create  RSS feed

 
Ranch Hand
Posts: 258
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Ranchers,

Hi after a long time !!!

I have create user operation done through my application for oracle database. I use below query,

CREATE user "V3 -- select test from dual" identified by test ;

Here username to be created i pass in double quotes. I am trying to check/confirm whether use of "" for user is avoiding any sql injection. Executing above created user named - V3 -- select test from dual

Based on this can i say in oracle we are safe for SQL injection if input to create, revoke command is passed enclosing quotes.

Thanks in advance for your views.
 
Saloon Keeper
Posts: 20649
122
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL injection looks like this:


Please note that there are single-quote (SQL quotes) and double-quote (Java string delimiters) next to each other there. We are doing string concatenation that in theory works like this:

which would output:

CREATE USER 'Fred' -- SELECT test FROM dual

However.

What about this one?



See the problem?
 
vijay jamadade
Ranch Hand
Posts: 258
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks Tim.

In oracle there is package dbms_assert which has methods like 'sql_object_name' which checks whether input is valid or not like below,

select DBMS_ASSERT.sql_object_name ('V$VERSION') from dual;

if i pass a query as input, it gives exception. Oracle says this is to avoid sql injection but i am not finding anybody using it for java code. I mean using it through jdbc connectivity. Any idea?

One more thing, we cant use preparedstatment here now so are we sure its gonna securely check input like 'V$VERSION' ?
 
Tim Holloway
Saloon Keeper
Posts: 20649
122
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have very great doubt in Oracle being able to detect all possible means of SQL Injection attack via a package. Industry Best Practice is to use PreparedStatements. Not only are they much more secure, they also are likely to get much better performance from the database server.

Whenever someone tells me that they "cannot" conform to recommended solutions, my standard response is this: Update your CV now. When you have management with such high levels of arrogance and such low levels of wisdom, you're not doing yourself any favours in staying there. You're probably not going to learn good skills there, and the long-term future of the company you work for is questionable when they take risks like that. Which means that it's a question of whether you leave the job before the job leaves you.
 
Master Rancher
Posts: 4041
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DBMS_ASSERT was created to provide some functions that can attempt to sanitize user input, for apps that do not use bind variables.

If you're writing anything new (or indeed anything you've written in the past 20 years or so) you should be using bind variables...in JDBC terms that would be a PreparedStatement.
You should not be relying on DBMS_ASSERT.
 
vijay jamadade
Ranch Hand
Posts: 258
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I understand. Your doubt is valid Tim. Achieving 100% security is difficult. Queries that I am trying to secure are DDL queries like create revoke which cant use bind parameters.

 
Tim Holloway
Saloon Keeper
Posts: 20649
122
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok. "Cannot" in the case where the software product doesn't permit it is a different thing. It's just that too often I hear "cannot" or "must not" in cases where some in-house "genius" has dictated some absurd constraints.

I would have expected that you would be able to do things like "create table" using a PreparedStatement, but then I'm not a big proponent of DDL in applications anyway. There are cases where it cannot be helped, but as a general rule, I recommend that schema changes be done offline. For schema interrogation you can rely on things like ResultSet metadata and the INFORMATION_SCHEMA tables without needing to do SQL DDL.

DDL by its nature is usually not done in primary business logic, which means that it's less likely to be exposed to SQL injection. You can further protect yourself by placing the schema mod stuff in a separate webapp that uses a different database userid (allowing the userid of the business app to be denied schema modification permissions).

Going back to your original example, however, you wouldn't normally do a "CREATE USER" in a webapp. Webapps don't normally use a distinct database userID for each web application user. Instead, they use a Connection Pool, and since all Pool Connections are identical, that means that they all connect to the database using the same database userid/password. And that userid/password therefore has to have rights equal to the greatest common denominator of all of the web application users' rights. Which is yet another case for keeping code that modifies schemas in a separate, more restricted webapp. Or doing your DBA functions offline.
 
get schwifty. tiny ad:
Create Edit Print & Convert PDF Using Free API with Java
https://coderanch.com/wiki/703735/Create-Convert-PDF-Free-Spire
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!