View original (French)

Query issue

Solved
Helene -  
 Helene -
Hello everyone,

I am currently working on a project in Java and for that, I created my database on phpMyAdmin. I connected my database to Eclipse, but the problem occurs when I want to add a given element as an argument to my function to my table (which currently has only one column).
I can add any string, but the issue is that it doesn't work when it's a variable.

String sql = "INSERT INTO javadb (personne) VALUES ('j')";

When I replace 'j' with nom, which is the name of my variable, it doesn't work. I've also tried:
.$nom.
$nom
"nom"
'nom'

Nothing works, I can't find the solution.
Any help would be greatly appreciated.
Best regards,

Hélène

2 answers

  1.
    NHenry Posted messages 2513 Registration date   Status Moderator Last intervention   387
     
    I think you need to concatenate your string ('Select ... ') with your variable to do what you want.

    --
    I mainly work in VB6 and VB.NET, with a bit of C#, but moderation often brings me to other languages.
    In VB.NET, make sure to enable "Option Explicit" and "Option Strict".
    0
    1. Solène
       
      Thank you for your response. The problem is that my variable is not stored in the array; it's actually a variable in Java. Well, I'm not sure I understood what you meant...
      0
    2. NHenry Posted messages 2513 Registration date   Status Moderator Last intervention   387
       
      Adding the content of a variable to a string is called concatenation.
      0
    3. KX Posted messages 19031 Status Moderator 3 020
       
      Attention: concatenation allows for SQL injection, so it should be avoided!

      Example: 
      String name = "'); DROP javadb; --";

      If I concatenate like this: 
      String sql = "INSERT INTO javadb (person) VALUES ('"+name+"')";

      It results in this: 
      INSERT INTO javadb (person) VALUES (''); DROP javadb; --')

      And bam! It deletes the entire table!
      0
  2. KX Posted messages 19031 Status Moderator 3 020
     
    Hello,

    You need a Prepared Statement.
    https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html 

    String sql = "INSERT INTO javadb (personne) VALUES (?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, nom); // "nom" is a String type variable ps.executeUpdate();

    --
    Trust does not exclude control.
    0
    1. Helene
       
      Hello,
      Thank you for your response, however I'm getting an error :/ :

      com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1343)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1260)
      at com.objis.demojdbc.DemoJdbc.sauverEnBase(DemoJdbc.java:34)
      at com.objis.demojdbc.DemoJdbc.main(DemoJdbc.java:12)
      0
    2. KX Posted messages 19031 Status Moderator 3 020
       
      You have: 
      at com.mysql.jdbc.Statement.executeUpdate
      which means you directly executed the query with ""?"" without replacing the value.

      If you had used the code I provided, in case of an error you should have seen: 
      at java.sql.PreparedStatement.executeUpdate()


      Moreover, your import must be incorrect; you likely imported com.mysql.jdbc.* but you need to import java.sql.* instead!
      0
    3. Helene
       
      Cool, it works. I didn't delete the statement and I got tangled up. Thanks :)
      0