hi
How can we solve SQL injection problem in JDBC ?
this means if we have a form with text field and the user must enter a number say 4 , instead he entered "4 or true" this will concatenated with the SQL query and return all records because of "or true"....
is there any solutions ?
i tried PreparedStatment and it words but not alwayes
good luck
Why is the topicstart all in bold?
Anyway,
> i tried PreparedStatment and it words but not alwayes
Give us at least one reproduceable example. I can imagine that a plain"SELECT * FROM table WHERE ID = " + enteredNumber
without any prechecks (gently parsing 'enteredNumber' to Integer, etc) might give those unexpected results. But I can't imagine how you could reproduce this using a PreparedStatement.
the post was in blod because of nothing !!
any way
i used preparedStatment like this
preparedStatment st=connection.getPreparedStatment("select * from table where id=?");
st.setString(1,someValue);
ResultSet rs=st.executeQuery();
this works , but not always
> When does it not work? I was asking for a
> reproduceable example.
when there is many where items like ,
where id=? and fName=? and lName=?
this doesnt work at all
> Using String as ID is bad practice tho. Use Number.
this is not the main problem , and i used Number in my application dont worry :)
I still don't see a reproduceable SQL injection example. So I can't help you further in solving the problem.
> and i used Number in my application dont worry :)
OK, if you're using Number in the application, why are you using PreparedStatement#setString() instead of setInt() or so?
> So, the so called SQL injection was just a mistake or
> misunderstanding of you? I still don't see a
> reproduceable example.
hey, how mentioned a reproduceable code, i was saying SQL injection problem not reproduceable code
,,
i clearfied this in my first post
if u didnt got what i mean u can google it
http://www.google.com
thanks
> i clearfied this in my first post
> if u didnt got what i mean u can google it
> http://www.google.com
> thanks
You didn't gently provide keywords, like I always do, so I cannot learn from you.
Well, with a "reproduceable example" I mean that you have to post a short but complete working code snippet which reproduces the problem. So that we can copy'n'paste it in our environment here and test/debug it ourself and then eventually confirm the SQL injection.
Summarized:
PreparedStaments sometimes cant deal with inputs that contains SQL statment like:
username
|-|
| employee1|
|-|
password
|-|
| medoo or true |
|-|
LOGIN
this without prepared statment wit be log in , but with prepared statment somtimes works and sometimes dont work , here and i othe jsp pages
did u got what i mean?
thanks
There is no need to feel ashamed or so, if it was certainly a mistake. Everyone can make mistakes. I just want a confirmation that it was just a mistake or misunderstanding from you and that there was no SQL injection when using a PreparedStatement. If you really can reproduce an SQL injection using a PreparedStatement, then this should be a serious bug which have to be reported. Keep this in mind.
> If you really can reproduce an SQL injection using a
> PreparedStatement, then this should be a serious bug
> which have to be reported. Keep this in mind.
dont worry BalusC , i can reprt it :)
by the way i am not ashamed , because u didnt understand me from the first post ,, and i said just forget this post because really u make me headache ,,, so if i was wrong i simply can ignore answering u,,
sorry for that , but this is the truth
thanks again ,,,
good luck
As long as you don't give reproduceable examples, and I don't see the bugreport being posted, I despitely don't believe you, no :)
I understood your posts clearly, but you was simply not complete and accurate in providing the information. I am almost sure that most of the others here at forum should agree with this.
Hey there, I could recommend you designing a Validator class that protects you from XSS and SQL injections. When you get an input from the user, you call a static validate() method from the class and check the input for bad data. You can replace the special characters with their unicode or html equivalent and check if you could parse the query input to any kind of numeric data.
Btw I've seen ASP solutions that even check for 'SELECT UNION' string in the query and if found, they totally ignored the code. So you can hardcore such kind of frequently used sql injection statements and protect yourself from them.
> call a static validate() method from the class and
> check the input for bad data. You can replace the
> special characters with their unicode or html
> equivalent and check if you could parse the query
> input to any kind of numeric data.
is there any recommend APIs or classes u have to do this functionality ?
thanks for ur answer
I still don't see how Prepared statements can be targeted with SQL injection.
So you insert the string 'drop database' into the username column of your database. Congratulations the hacker now has a user with the name bob'; drop database;
Prepared statements ensure that the values passed to a sql statement are exactly that - JUST VALUES.
Watching for common sql injection tricks is all very well, but invariably you will forget something, or someone will find a way around it. Prepared statements are to my mind still the preferred approach.
> So you insert the string 'drop database' into the
> username column of your database. Congratulations
> the hacker now has a user with the name bob'; drop
> database;
no, i meant that he will enter sometging like bob or true
and this will alwys for example authenticate him to enter the system ,, thats all
thanks for ur answers...........
>no, i meant that he will enter sometging like bob or true
Exactly.
And a prepared statement would ALWAYS translate it into
where username = 'bob or true'
The SQL itself can never be modified when using a prepared statement.
If you were making the sql like this
String sql = "Select id from usersTable where username = '" + enteredUserName + "'";
That is open to sql injection.
sql = "Select id from usersTable where username = ?"
preparedStatement.setString(1, enteredUserName);
This approach is NEVER open to sql injection even if you do type in the value "bob or true"
