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.

score 1 · Answer 1 · answered May 25 '14 at 23:18

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 '14 at 23:18

woot

7,406
2
36
55

You could also remove the saving to a variable and make it a one liner. Just my $0.02 – Cole Tobin May 25 '14 at 23:28
@ColeJohnson True, I did it that way because often people run the same prepared statement repeatedly and it is more efficient to reuse it. – woot May 25 '14 at 23:40

score 0 · Accepted Answer · answered May 25 '14 at 23:07

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 '14 at 23:07

Cole Tobin

9,206
15
49
74

Thanks that did the trick I've been at it for hours now... Thankyou so much! – Anwer May 25 '14 at 23:09
@Anwer I _highly_ suggest you read this also: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Cole Tobin May 25 '14 at 23:23

Rami Del Toro · Answer 3 · 2014-06-14T00:23:53.577

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);

How to Use Program Values To Search In SQL SERVER

3 Answers3