Sql syntax problem asking me to check my SQL version to use near update restaurant set date= customerid= customername= totalc at line 1

Question

1.00/5 (1 vote)

See more:

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         
  
        try { Class.forName("java.sql.DriverManager");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/12C","root","tiger"); 
            Statement stmt = con.createStatement(); {
                
             
               
                 String query="update restaurant set  Date="+dtf.getText()+", CustomerID="+iddtf.getText()+", CustomerName="+cntf.getText()+", TotalCost="+ttf.getText()+", PhoneNumber="+pntf.getText()+", Address="+atf.getText()+"where CustomerID="+idtf.getText()+";"; 
              
           stmt.executeUpdate(query);
                JOptionPane.showMessageDialog(this, "Record Updated");
            stmt.close();
                    con.close();
        } 
} catch(Exception e)
{ JOptionPane.showMessageDialog(null, e.getMessage());
} 
        // TODO add your handling code here:
    }

What I have tried:

Many things like changing codes

Posted 2-Sep-19 9:05am

YOMANIGGA

Updated 6-Sep-19 4:52am

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Answer 1 · 2019-09-02T09:41:00

There are two errors with your SQL statement:

String query="update restaurant set  Date="+dtf.getText()+", CustomerID="+iddtf.getText()+", CustomerName="+cntf.getText()+", TotalCost="+ttf.getText()+", PhoneNumber="+pntf.getText()+", Address="+atf.getText()+"where CustomerID="+idtf.getText()+";";

First: Too poorly written to understand what is going on there, secondly: SQL Injection. Let us try to solve the first problem, and I will let you solve the second one yourself.

First thing is, that your SQL is clearly not understandable. What exactly is happening there?

SQL

UPDATE [Restaurant] 
     SET Data           = 'date', -- see the single quotes around string data
         CustomerID     = '1',
         CustomerName   = 'Name',
         TotalCost      ='123',
         PhoneNumber    = '123456789',
         Address        = 'Some Street'
     WHERE CustomerId   = '1';

Can this make sense? Of course it shows what you are trying to do. In your heavily-concatenated string, nothing is making any sense. So what to do? In Java, you can use String.format() function to format the strings, but that does not solve the problem. For SQL, you need to not only format the string but also need to make sure that your strings do not contain any unsanitized input—which leads to SQL Injection, see the link I provided—and that is what I would recommend you to use.

Your solution is simply to quote the data that you are passing, because that is missing and SQL requires that around string types (unless you are sending the data in native types, which I cannot see).

Check out how this parameterized query works in Java:
Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)[^]
java - passing parameters to a JDBC PreparedStatement - Stack Overflow[^]
executeQuery Method (java.lang.String) - SQL Server | Microsoft Docs[^]

You get the point, right?

Read more about SQL Injection here, SQL injection - Wikipedia[^]

MadMyche · Answer 2 · 2019-09-02T09:51:00

From what I can tell, it looks like what is happening in your case is that none of the values you are putting in are being assigned as values as they are not wrapped in single quotes

I can definitely tell you that this is not the way to do; piecing together strings and user input is the #1 source of SQL Injection, and is pretty sad that people do this everyday when this Top 10 Vulnerability was identified over 20 years ago.

The correct way to do this would be to create a Prepared Statement, which will have your query text in it and use placeholders for the value.
The next step to preparing the statement is to assign the values to those placeholders.

I am not a Java developer, but this should get you started.

Java

PreparedStatement updateStatement = null;

string updateQuery = "update restaurant set  Date = ?, CustomerID = ?, CustomerName = ?, TotalCost = ?, PhoneNumber = ?, Address = ? where CustomerID = ?";

try {
  // create the statement based off of query
  updateStatement = con.prepareStatement(updateQuery);

  // assign variables to the placeholders
  updateStatement.setString(1, dtf.getText());
  updateStatement.setString(2, iddtf.getText());
  updateStatement.setString(3, cntf.getText());
  updateStatement.setString(4, ttf.getText());
  updateStatement.setString(5, pntf.getText());
  updateStatement.setString(6, atf.getText());
  updateStatement.setString(7, idtf.getText());

  // execute the update
  updateStatement.executeUpdate();

References:
Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)[^]
Java PreparedStatement - a SQL UPDATE example | alvinalexander.com[^]

Patrice T · Answer 3 · 2019-09-02T10:03:00

Your SQL command is subject to SQL injection and have many other problems:

First thing store the query in a variable and echo it to see what it look like with user data. Debugger can help too.
When data is a string, you need to change the query from

C#

CustomerName="+cntf.getText()+",

to

C#

CustomerName='"+cntf.getText()+"',

The single quotes says to sql that what is in between is not sql.

You need a space before the 'where'

C#

Address="+atf.getText()+"where

C#

Address="+atf.getText()+" where

and quotes around the customer address

C#

Address='"+atf.getText()+"' where

if date is not a number, you have another problem too.
All this just to get you started.
-----

C#

String query="update restaurant set  Date="+dtf.getText()+", CustomerID="+iddtf.getText()+", CustomerName="+cntf.getText()+", TotalCost="+ttf.getText()+", PhoneNumber="+pntf.getText()+", Address="+atf.getText()+"where CustomerID="+idtf.getText()+";";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

YOMANIGGA · Answer 4 · 2019-09-06T04:52:00

Solution 4

Thx bros fixed it with single quotes

Posted 6-Sep-19 4:52am

YOMANIGGA

Comments

Patrice T 6-Sep-19 11:54am

accept useful solutions, so question will be closed as solved.

Richard Deeming 6-Sep-19 14:41pm

No, you've bodged it with single quotes. You've left the underlying critical security vulnerability intact.

Your database won't survive five minutes in the real world. SQL Injection is so simple to exploit, a three-year-old child can do it[^].

Do yourself a favour and read the links that others have provided. Then go back and fix your code properly.

Sql syntax problem asking me to check my SQL version to use near update restaurant set date= customerid= customername= totalc at line 1

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0