having trouble inserting values into table with syntax error

Dramus17

Everytime at around "composedLine = String.format("%s, %s, %s, %s, %s", composedLine, values[0], values[1], values[2], values[3]);" it produces "INSERT INTO airport VALUES (, ABR, Aberdeen Regional Airport, Aberdeen"

instead of "INSERT INTO airport VALUES (ABR, Aberdeen Regional Airport, Aberdeen"

which causes a syntax error when I use executeupdate due to the "," before the ABR.

import java.io.*;
import java.sql.*;
import java.util.*;

public class UsaDelayFlight {
    


        
    

    public static Connection connectToDatabase(String user, String password, String database) {
            System.out.println("------ Testing PostgreSQL JDBC Connection ------");
            Connection connection = null;
            try {
                String protocol = "jdbc:postgresql://";
                String dbName = "";
                String fullURL = protocol + database + dbName + user;
                connection = DriverManager.getConnection(fullURL, user, password);
            } catch (SQLException e) {
                String errorMsg = e.getMessage();
                if (errorMsg.contains("authentication failed")) {
                    System.out.println("ERROR: \tDatabase password is incorrect. Have you changed the password string above?");
                    System.out.println("\n\tMake sure you are NOT using your university password.\n"
                            + "\tYou need to use the password that was emailed to you!");
                } else {
                    System.out.println("Connection failed! Check output console.");
                    e.printStackTrace();
                }
            }
            return connection;
        }
        
        public static void dropTable(Connection connection, String table) throws SQLException  {
            Statement st = null;
            try {
            st = connection.createStatement();
            boolean result = st.execute("DROP TABLE IF EXISTS " + table);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            st.close();
        }
        
        public static void createTable(Connection connection, String tableDescription) throws SQLException  {
            Statement st = null;
            try {
            st = connection.createStatement();
            boolean result = st.execute("CREATE TABLE IF NOT EXISTS " + tableDescription);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            st.close();
        }
        
        public static ResultSet executeQuery(Connection connection, String query) {
             System.out.println("DEBUG: Executing query...");
             try {
             Statement st = connection.createStatement();
             ResultSet rs = st.executeQuery(query);
             return rs;
             } catch (SQLException e) {
             e.printStackTrace();
             return null;
             }
             }
        
        public static int insertIntoTableFromFile(Connection connection, String table, 
                String filename) {
            int numRows = 0;
            String currentLine = null;
            try {
            BufferedReader br = new BufferedReader(new FileReader(filename));
            Statement st = connection.createStatement();
            // Read in each line of the file until we reach the end.
            while ((currentLine = br.readLine()) != null) {
            String[] values = currentLine.split(",");
            System.out.println(Arrays.toString(values));
            String composedLine = "INSERT INTO " + table + " VALUES (";
            //String r = String.format("formatted values are %s", composedLine);
            composedLine = String.format("%s, %s, %s, %s", composedLine,
                     values[0], values[1], values[2], values[3]);
            System.out.println(composedLine);
             //. . .
            // Finally, execute the entire composed line.
            numRows = st.executeUpdate(composedLine);
            }
            } catch (Exception e) {
            e.printStackTrace();
            }
            return numRows;
        }
        
        
            
            // NOTE: You will need to change some variables from START to END.
                public static void main(String[] argv) throws SQLException {
                    // START
                    // Enter your username.
                    String user = "";
                    // Enter your database password, NOT your university password.
                    String password = "";
                    
                    /** IMPORTANT: If you are using NoMachine, you can leave this as it is.
                     * 
                     *  Otherwise, if you are using your OWN COMPUTER with TUNNELLING:
                     *      1) Delete the original database string and 
                     *      2) Remove the '//' in front of the second database string.
                     */
                    String database = "";
                    //String database = "";
                    // END
                    
                    Connection connection = connectToDatabase(user, password, database);
                    if (connection != null) {
                        System.out.println("SUCCESS: You made it!"
                                + "\n\t You can now take control of your database!\n");
                    } else {
                        System.out.println("ERROR: \tFailed to make connection!");
                        System.exit(1);
                    }
                    // Now we're ready to use the DB. You may add your code below this line.
                    createTable(connection, "delayedFlights (ID_of_Delayed_Flight varchar(15) not null, Month varchar(10), "
                            + "DayofMonth int, DayofWeek int, DepTime timestamp, ScheduledDepTime timestamp, ArrTime int,"
                            + "ScheduledArrTime timestamp, UniqueCarrier varchar(15) not null, FlightNum int, ActualFlightTime timestamp,"
                            + "scheduledFlightTime timestamp, AirTime timestamp, ArrDelay timestamp, DepDelay timestamp, Orig varchar(15),"
                            + "Dest varchar(15), Distance int, primary key (ID_of_Delayed_Flight), unique (UniqueCarrier));");
                    
                    createTable(connection, "airport (airportCode varchar(15) not null, "
                            + "airportName varchar(15), City varchar(15), State varchar(15), primary key (airportCode));");
                    
                    insertIntoTableFromFile(connection, "airport", "airport");
                    String query = "SELECT * FROM delayedFlights;";
                     ResultSet rs = executeQuery(connection, query);
                     try {
                     while (rs.next()) {
                     System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
                     }
                     } catch (SQLException e) {
                    e.printStackTrace();
                     }
                     rs.close();
                }
                
    
    }
rzwitserloot

This code is a security vulnerability. Specifically, SQL injection. This is not how you do it.

The correct way also solves your problem in passing. Thus, solution: Do it the correct way, solves all your problems.

Correct way:

PreparedStatement ps = con.prepareStatement("INSERT INTO " + table + " VALUES (?, ?, ?, ?)");
ps.setString(1, values[0]);
ps.setString(2, values[1]);
ps.setString(3, values[2]);
ps.setString(4, values[3]);
ps.executeUpdate();

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

"Syntax error in insert into statement" when inserting image into Access table

"Error: invalid input syntax for integer:" when inserting NULL values for an SMALLINT column in a Redshift table?

Having some trouble with the data.table syntax

Having trouble with Postgres unnest array syntax

Having Trouble Inserting Row Into Table

Error Inserting: no such table error

SQL Inserting Syntax Error

Error in Inserting Values into PostgreSQL table through php

No such table - error inserting values in SQLite Android

Error inserting values to a table in MySql

Inserting values into selected column trouble

Having trouble with Playpen error

Having trouble with microsoft query syntax

Syntax Error when Inserting New Data Into Table

Error in your SQL syntax while Inserting to table

SQL Trouble Inserting Data into Table

syntax error: inserting LPAREN

Syntax error when inserting to MySQL table in PHP

NOOB C Programming Question: I'm having trouble with inserting values that are being read from a file into other variables throughout my code

PHP - Having trouble inserting dates from a date column with null values. Values are inside an array

I'm new to using MYSQL and I'm having trouble locating my syntax error

I'm having trouble understanding a syntax error in Python 3.7

to_sql "Incorrect syntax" error on inserting DataFrame to [schema].[table]

SQL OperationalError: near "%": syntax error when using inserting values into a table with "executemany"

having trouble inserting a value into a numpy array

SQL error with inserting values into new table

C# Syntax Error. Having trouble making an incremental counter in Unity for OnMouseDown

SQL syntax inserting into table

Oracle SQL having troubles inserting values into a table