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();
}
}
CodePudding user response：
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();