How to Use Program Values To Search In SQL SERVER

Question

I am trying to use some values taken from the user in an SQL query and trying to search on basis of that input. I did a little research and found this on web.

st.executeQuery("SELECT * FROM Users WHERE UserName LIKE 'userName%'");

I then tried changing it to

st.executeQuery("SELECT * FROM Users WHERE UserName='userName%'");

But that didn't work. Also I've tried using '@userName' but that doesn't work either. So Now I am here since clearly I am making some obvious mistake or none of the above methods are correct. Now if I hardcode the values

st.executeQuery("SELECT * FROM Users WHERE UserName='Anwer'");

it works fine. So I think I am making mistake on how to use the program's values. I am using SQL Management Studio 2012 Programming Language Java.

woot · Accepted Answer · 2014-05-25 23:18:07Z

1

You should use a bind value instead of inlining the string. That's prone to SQL injection attacks (hackers cleverly constructing an input value that lets them run arbitrary SQL).

Something like:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM Users WHERE UserName=?");
ps.setString(1, "Anwer");
ResultSet resultSet = ps.executeQuery();

answered May 25, 2014 at 23:18

woot

7,6263 gold badges38 silver badges55 bronze badges

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

2 Comments

Cole Tobin Over a year ago

You could also remove the saving to a variable and make it a one liner. Just my $0.02

woot Over a year ago

@ColeJohnson True, I did it that way because often people run the same prepared statement repeatedly and it is more efficient to reuse it.

Cole Tobin · Accepted Answer · 2014-05-25 23:07:35Z

0

If your user name is stored in a variable called userName, you would do this:

st.executeQuery("SELECT * FROM Users WHERE UserName='" + userName + "'");

answered May 25, 2014 at 23:07

Cole Tobin

9,44815 gold badges52 silver badges77 bronze badges

2 Comments

Anwer Over a year ago

Thanks that did the trick I've been at it for hours now... Thankyou so much!

Cole Tobin Over a year ago

@Anwer I highly suggest you read this also: stackoverflow.com/questions/60174/…

Rami Del Toro · Accepted Answer · 2014-06-14 00:23:53Z

0

Of course PreparedStatements are a great option for many implementations. Depending on your use case,keep in mind additionally for Stored Procedures. Stored Procedures can have the PL/SQL compiled, prepared and optimized by the Database and have it called by your application. Example is shown below

String getUser= "{call getUser(?,?,?)}";
callableStatement = dbConnection.prepareCall(getUser);
callableStatement.setString(1, "sampleUserName");
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.executeUpdate(); //Excecute Stored Procedure

String firstName = callableStatement.getString(2);
String LastName = callableStatement.getString(3);

edited Jun 14, 2014 at 0:23

answered May 25, 2014 at 23:40

Rami Del Toro

1,14111 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to Use Program Values To Search In SQL SERVER

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related