SQl-Injection lesson 4 not deleting created row

[SturmCamper]

In the 4th lesson the goal is to alter a table by adding new Row ('phone').
While it works for the first time, the added row never is deleted.

---

---

The main problem accrues when u want to retry the lesson. U are able to create custom rows and still get an approval.
Also if WebGoat is used by multiple people it may lead to confusions, since they aren't able to complete the task properly.

---

---

WebGoat/webgoat-lessons/sql-injection/src/main/java/org/owasp/webgoat/sql_injection/introduction/SqlInjectionLesson4.java

Lines 56 to 75 in e40a26f

	protected AttackResult injectableQuery(String query) {
	try (Connection connection = dataSource.getConnection()) {
	try (Statement statement = connection.createStatement(TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY)) {
	statement.executeUpdate(query);
	connection.commit();
	ResultSet results = statement.executeQuery("SELECT phone from employees;");
	StringBuffer output = new StringBuffer();
	// user completes lesson if column phone exists
	if (results.first()) {
	output.append("<span class='feedback-positive'>" + query + "</span>");
	return success(this).output(output.toString()).build();
	} else {
	return failed(this).output(output.toString()).build();
	}
	} catch (SQLException sqle) {
	return failed(this).output(sqle.getMessage()).build();
	}
	} catch (Exception e) {
	return failed(this).output(this.getClass().getName() + " : " + e.getMessage()).build();
	}

[nbaars]

We can solve this one for now by making the column name random, but we need is to have each user have their schema in the database. This way we solve it once for all. Otherwise, it will be "hacky" in a way we need to let the user create a random column name, etc.

Until now we have already split the database user administration from the lesson but the lessons need a different schema for each user.

We would probably have to create a specific DataSource bean which will before it creates a statements alters the current schema. This way it is transparent while building a lesson.