SQL Problems 3

[yigit]

hi everyone,
I'm trying to delete a row with a certain IDnumber names 'IDtoDelete'. My delete part seems not to work;
is something missing?(IDtoDelete is read from a textfield as a string);

public void DeleteFrom(String IDtoDelete) {
try {
deleteStmt = con.prepareStatement(
"DELETE FROM UserInfo WHERE UserID= 'IDtoDelete'" );
} catch (SQLException ex) {
ex.printStackTrace();
}
}


apart from this, how do i assign buttons that help to move around the database table and display the current row's elements.(for example move prev,move forw : going to previous row or next rown and displaying them)

thanks in advance;
cheers

 

[Diancecht]

Maybe you need to look at some documentation about PreparedStatemetns:

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Cheers,
Dian

 

[yigit]

i checked but that site wasnt explaining alot of information. After checking a while i found that i have to first fetch a row then delete it but it doesn't sound right. Is there no way to delete a specific row where the user inputs the row's ID to be deleted. I'm really stuck here in this small problem...

 

[Diancecht]

First of all, you'd need to actually include your variable into the statement

public void DeleteFrom(String IDtoDelete) {
        try {
            deleteStmt = con.prepareStatement(
       "DELETE FROM UserInfo WHERE UserID= '"+IDtoDelete+"'" );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

Cheers,
Dian

 

[feherke]

Hi

IDtoDelete is read from a textfield as a string

Dian, and what if the user enters this in the text field :

' or 1=1 or ''='

Which concatenated with the String literal will result :

DELETE FROM UserInfo WHERE UserID= '[red]' or 1=1 or ''='[/red]'

Feherke.

http://rootshell.be/~feherke/

 

[timw]

But as you'll find in the Javadocs, the correct way to set values in prepared statements is like this:-

public void DeleteFrom(String IDtoDelete) {
        try {
            deleteStmt = con.prepareStatement(
       "DELETE FROM UserInfo WHERE UserID=?" );
            deleteStmt.setString(1, IDtoDelete);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

This also is supposed to protect against 'injection attacks'. (The value of your IDtoDelete could contain a string which tags on extra SQL to your statement to do malicious things. Simply building the SQL string like before opens you to this threat. Using the ? substitution doesn't).

Tim

 

[Diancecht]

That's an SQL injection attack, the main reason, from my point of view, for using PreparedStatement as I suggested in my first post.

But who knows, maybe it's a reporting application run by the DBA and the OP doesn't care about attacks.

Cheers,
Dian

 

[feherke]

Hi

If the method is called DeleteFrom, I would say all deleting should be done there. Is there an [tt]executeQuery()[/tt] outside the method ? Anyway, I would say should be inside the method :

public void DeleteFrom(String IDtoDelete) {
  try {
    deleteStmt = con.prepareStatement(
       "DELETE FROM UserInfo WHERE UserID=?" );
    deleteStmt.setString(1, IDtoDelete);
    [red]deleteStmt.executeQuery();[/red]
  } catch (SQLException ex) {
    ex.printStackTrace();
  }
}

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Sorry. Should be [tt]executeUpdate()[/tt], not [tt]executeQuery()[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[yigit]

thanks feherke..
it works now...thanks everybody for their help.

 

[Diancecht]

Nice to know it's working, yigit.

Now your choice is get the fish or read the documentation and become a fisherman

Cheers,
Dian

 

[yigit]

I know i bother you ppl with all this small, insignificant questions but to be a fisherman, first you have to know what a fish is(I learned how to catch it). In my position im like a person who woke up in the middle of the sea remembering nothing and trying to fish.

 

[Diancecht]

I don't think you bother anyone at all, at least not me. Each one has iss questions, no one can judge that.

I was just doing the suggestion. Of course, if you're not planning to program Java anymore it's not woth reading at all, but if you're thinking on going on manipulating databases from Java, you will save time and effort by doing it.

Cheers,
Dian

 

[timw]

You make an effort to ask specific questions. And you break your problems down into manageable ones for us to answer. And you reward those who help you.

Nope, you don't bother me either.

Tim

 

[feherke]

Hi

No problem yigit. We all know that Java documentation is huge and we do not expect that you will understand it. At first read. A hint : use simultaneously java.sun.com and google.com. The first will give the syntax, the second the examples.

Keep working. We would like to learn something from you too someday.

Feherke.

http://rootshell.be/~feherke/