"UPDATE usuarios SET Telefono= "+tel+", Domicilio= "+dom+", Contraseña= "+contra+" WHERE Id="+id;
This is the sentence that I use when updating the database, the data of the columns and its name are correct but it sends me the following error
com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that correcponds to your MariaDB server version
Thanks in advance
Columns that are text type, when assigning values, must be enclosed in single quotes (''). While columns that are numeric type, do not need quotes.
Assuming that the Phone, Address, and Password columns are text type
"UPDATE usuarios SET Telefono= '"+tel+"', Domicilio= '"+dom+"', Contraseña= '"+contra+"' WHERE Id="+id;
If it were the case that Address was of numeric type
"UPDATE usuarios SET Telefono= '"+tel+"', Domicilio= "+dom+", Contraseña= '"+contra+"' WHERE Id="+id;
I suppose you have it in auto-increment (it becomes numeric), but if the case were not so ... you know the rule. ('')
We insist a lot on the problem of SQL injection when we talk about PHP, but SQL injection is a latent danger also in Java or any other programming language.
If any of the values used in this query are taken from an external source, this is a vulnerable code:
"UPDATE usuarios SET
Telefono= '"+tel+"',
Domicilio= '"+dom+"',
Contraseña= '"+contra+"'
WHERE Id="+id;
Why is this query vulnerable? Suppose that the value id is taken from a TextField or from another external source and a malicious user writes this:
1; DELETE FROM usuarios;
Do not try the previous code! We would be sending two queries of ours% of UPDATE with the value id=1 and another query that would erase all the users table. And that is one of the least bad injections ... since there are worse ones.
For these cases Java recommends using prepared queries.
In fact, the Java documentation has a tutorial (almost) very complete on the subject.
We create our query, but without putting the values in it directly. There is precisely the problem of Injection:
String strSQL= "UPDATE usuarios SET
Telefono= ?,
Domicilio= ?,
Contraseña= ? //No usaría la ñ en nombres de columnas
WHERE Id= ?";
As we can see, the values are replaced by signs of ? . With PreparedStatement what we do is delegate the preparation of the query to the DBMS.
We create our variable of type PreparedStatement :
PreparedStatement ps = null;
Now we invoke the prepareStatement method of the connection object to the database (in this example the connection object would be called con ) and we pass the string of the query created above.
ps = con.prepareStatement(strSQL);
Now in ps we have to say so an object PreparedStatement active% in the connection that is waiting for we pass the parameters. This is where for every ? of the previous query we must pass a value. The methods setInt , setString serve to indicate what type that die is in the database. And the number, starting with the 1 indicates the order in which each value appears in strSQL :
ps.setString(1,tel);
ps.setString(2,dom);
ps.setString(3,contra);
ps.setInt(4,id);
Since everything is ready, we execute:
int intTotal = ps.executeUpdate();
Given that executeUpdate() would return in this case the totality of updated records, we can store it in a variable ( intTotal ) in case we want to be informed about the number of records that were updated.
That way we have a safe and written code as recommended by the documentation.
Prepared queries have other advantages, such as the reuse of ps to send more queries, etc. It is important to learn its use.
It says that you have an error in the sentence, it verifies that the fields are all correct, that is to say, that everything is well written. Check that the ID is an INT and above all REMEMBER that the varchars as they can be domicile must have quotes. I'm sure it must be for the quotation marks because you'll have already checked the fields and so on, so where you have Varchars put the single quotes "'", for example like this.
"UPDATE usuarios SET Telefono= '"+tel+"', Domicilio= '"+dom+"', Contraseña= '"+contra+"' WHERE Id="+id;
Greetings.