Passing Argument from Java to Sql

Question

I've worked with sqlplus and Java but never together until now. I'm having difficulty in getting a command line argument into sql to return a regular expression-specific list of users. My if statement and error is below. I believe the program is actually searching the list of users for "^A". Any tips on resolving this would be greatly appreciated.

else if (num == 1 && !args[0].equals("-n"))
{
     String cmd = "select * from all_users where regexp_like(username, " + args[0] + ", 'i') order by username";
     System.out.println(cmd);
     String users[] = ora.doSql(cmd);
     for (String u: users)
         System.out.println(u);
 }

My results:

java ShowUsers ^A
select * from all_users where regexp_like(username, ^A, 'i') order by username
select * from all_users where regexp_like(username, ^A, 'i') order by username
                                                *
ERROR at line 1:
ORA-00936: missing expression

Michelle, there's a great online tutorial for prepared statements at docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html and I think it would be worthwhile for you to try to rewrite your code in the way that this tutorial describes. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented Aug 29, 2016 at 19:30

Pritam Banerjee · Accepted Answer · 2016-08-29 19:01:18Z

2

You should never use string concatenation for JDBC queries as they are vulnerable to SQL injection attacks.

Instead you should use prepared statements.

If you have to use String concatenation you can follow whatever @vkp mentioned in the comments.

answered Aug 29, 2016 at 19:01

Pritam Banerjee

19.1k10 gold badges97 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dawood ibn Kareem Over a year ago

Perhaps OP is working on building some kind of SQL console; in which case vulnerability to SQL injection is kind of a requirement. It seems clear to me that code that displays a list of users is unlikely to be code that can be run by "Jo User on the Internet".

Michelle Over a year ago

Thank you. I definitely came across prepared statements, but we are only supposed to use knowledge we have learned in class thus far - which of course just started. Adding the single quotation marks, as suggested by Plirkee, worked perfectly.

PKey · Accepted Answer · 2016-08-29 19:12:36Z

1

Quotes are missing inside refexp_like. Try the following:

else if (num == 1 && !args[0].equals("-n"))
{
     String cmd = "select * from all_users where regexp_like(username, '" + args[0] + "', 'i') order by username";
     System.out.println(cmd);
     String users[] = ora.doSql(cmd);
     for (String u: users)
         System.out.println(u);
 }

However, as mentioned by all others, this is considered a bad practice and your code will be prone to sql injection.
So you should really use prepared statements instead.

edited Aug 29, 2016 at 19:12

answered Aug 29, 2016 at 19:03

PKey

3,8712 gold badges21 silver badges45 bronze badges

Collectives™ on Stack Overflow

Passing Argument from Java to Sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related