CODEWITHSUNDEEP
javasqlsql-server
Anwer
Anwer

Reputation: 11

How to Use Program Values To Search In SQL SERVER

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.

Upvotes: 0

Views: 72

Answers (3)

Rami Del Toro
Rami Del Toro

Reputation: 1150

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

Upvotes: 0

woot
woot

Reputation: 7606

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

Upvotes: 1

Cole Tobin
Cole Tobin

Reputation: 9430

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

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

Upvotes: 0

Related Questions