Java 學習記錄126 — SQL Injection Attacks and Prepared Statements

public class Main {
public static void main(String[] args) {
Datasource dataSource = new Datasource();
if (!dataSource.open()) {
System.out.println("Can't open datasource");
return;
}
Scanner scanner = new Scanner(System.in);
System.out.println("Enter a song title to search: ");
String title = scanner.nextLine();
List<SongArtist> songArtists2 = dataSource.querySongInfoView(title);
if (songArtists2.isEmpty()) {
System.out.println("Couldn't find the artist for the song");
return;
}
for (SongArtist artist : songArtists2) {
System.out.println("FROM VIEW - " + artist);
}
dataSource.close();
}
}

Connect to music.db success!

Enter a song title:

go your own way

SELECT name, album, track FROM artist_list WHERE LOWER (title) = “go your own way”

FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = Greatest Hits, track = 2}

FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = Rumours, track = 5}

FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = The Dance, track = 15}

FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = The Very Best Of, track = 1}

Connect to music.db success!

Enter a song title to search:

go your own way” or 1=1 or “

SELECT name, album, track FROM artist_list WHERE LOWER (title) = “go your own way”or 1=1 or””

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 1}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 2}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 3}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 4}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 5}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 6}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 7}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 8}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 9}

FROM VIEW — SongArtist{artistName = 1000 Maniacs, albumName = Our Time in Eden, track = 10}

(略刪)

public class Datasource {    public static final String QUERY_VIEW_SONG_INFO_PREP = "SELECT " + COLUMN_ARTIST_NAME + ", " +
COLUMN_SONG_ALBUM + ", " + COLUMN_SONG_TRACK + " FROM " + TABLE_ARTIST_SONG_VIEW +
" WHERE LOWER (" + COLUMN_SONG_TITLE + ") = ?";
private Connection conn; private PreparedStatement querySongInfoView; public boolean open() {
try {
conn = DriverManager.getConnection(CONNECTION_STRING);
querySongInfoView = conn.prepareStatement(QUERY_VIEW_SONG_INFO_PREP);
System.out.println("Connect to " + DB_Name + " success!");
return true;
} catch (SQLException e) {
System.out.println("Couldn't connect ot database: " + e.getMessage());
return false;
}
}
public void close() {
try {
if (querySongInfoView != null) {
querySongInfoView.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("Couldn't close connection: " + e.getMessage());
}
}
public List<SongArtist> querySongInfoView(String song_title) { try {
querySongInfoView.setString(1, song_title);
ResultSet resultSet = querySongInfoView.executeQuery();
List<SongArtist> songArtists = new ArrayList<>();
while (resultSet.next()) {
SongArtist songArtist = new SongArtist();
songArtist.setArtistName(resultSet.getString(1));
songArtist.setAlbumName(resultSet.getString(2));
songArtist.setTrack(resultSet.getInt(3));
songArtists.add(songArtist);
}
return songArtists;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return null;
}
}
}

Connect to music.db success!

Enter a song title to search:

go your own way “ or 1=1 or “

Couldn’t find the artist for the song

上面代碼全都紀錄在我的 Github

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store