Web Breakthrough Game (Riddle Webgame)--sql infused Pandora's Box

Objective:
Prior to writing a Web page game (similar to Riddle game), in addition to the hope that you can experience my game outside. Also willing to share in the process of writing this web game, learn some knowledge.
Web development beginners tend to overlook some common vulnerabilities, such as SQL injection attacks and XSS attacks. This article will briefly describe the principle of SQL injection attack, and share the next level design, which in the case of opening Pandora Magic Box, but also very good to limit the harm.

Effect Show:
First lay the advertisement: The entrance of the Web entry game ( please click on me, ^_^).
The idea of this article was carried out in the 11th--forgetful professor.
　　
It is very straightforward to present a login dialog box to test whether the player can bypass the login verification by unconventional means.

SQL injection attacks:
Although the SQL injection attack is a cliché, but still have to nag a few words "science" a bit, ^_^.
After the application obtains the data submitted by the user, some of the SQL statements are spliced and executed. If the user submits data that contains SQL execution commands , the program does not filter and securely validate the data . This makes it possible to bypass validation and get data, even injecting malicious code, causing data to be tampered with and lost.
• Take user login as an example
The user data model for the service is as follows:

Table Tb_user (username varchar (+), password varchar (32));

The login verification SQL for the service is as follows:

SELECT * from Tb_user WHERE username = '? ' and password = '? ';

The login input box is shown in the diagram:
　　
Its corresponding form form is:

<form method= "POST" > User name: <input name= "username"/> Password: <input name= "password" type= "password"/></ Form>

Hacker as long as the field content is cleverly designed in the form form, the SQL Execution command is injected to bypass data validation.
For example, username field, fill in as: ' or 1 = 1 #.
So on the server side, the final stitching of SQL is:

SELECT * from Tb_user WHERE username = ' or 1 = 1 # ' and password= '? '

Because the character ' # ' is represented in the SQL specification, the ' # ' character specifier until all characters at the end of the line are ignored.
So the final SQL is equivalent to the following:

SELECT * from Tb_user WHERE username = ' or 1 = 1

User data will be returned, if the attempt to log in is the administrator background, that hacker will easily get to the administrator's permission, which is very scary.
Outsmart however persuasive, now that you know what the attack principle of SQL injection is? Then the precautionary measures are targeted, do not trust the user submitted data , do a good job of filtering and verification.

Level design:
This is to examine the player's cognitive skills in SQL injection. So the simulation builds a landing window that accepts open answers .
How to verify the answer? 1). perform parsing of simulated SQL . 2). run the real SQL directly.
For scenario one, the workload is large, multiple SQL commands need to support, it is possible to overwrite all the solutions, a little outweigh the gains.
For scenario two, it is easy to implement, but poses a potential risk to the system, such as injecting a dangerous command such as a drop tables.
The tradeoff is the second option, and the risk control can be controlled as well.
　　The service is written in Java, and if you want to let go of the SQL Injection vulnerability qualification, you cannot use mybatis/hibernate these ORM frameworks, because these frameworks have helped us do the work of escape data.
Let's go back to the Stone Age and use JDBC directly to implement the code as follows:

/**** constructs a classic SQL injection attack * @param username user input username * @param password user input password * @return */public boolean verifysqlinject (String use　　Rname, String password) {Connection Connection = null;　　　　try {//*) dynamically loaded into MySQL Driver driver class class.forname ("Com.mysql.jdbc.Driver");　　　　*) Get DB Connection Connection = drivermanager.getconnection (Dburl, Dbusername, Dbpassword);　　　　Statement stmt = Connection.createstatement ();  String sql = String.Format ("SELECT *" + "from Tb_virtual_user" + "WHERE username　　　　= '%s ' and password = '%s ', username, password);　　　　ResultSet rs = stmt.executequery (SQL);　　　　　　if (Rs.next ()) {stmt.close ();　　　　*) Login successful return true;　　}} catch (ClassNotFoundException e) {e.printstacktrace ();　　} catch (SQLException e) {e.printstacktrace ();　　　　　　} finally {if (connection! = null) {try {connection.close ();　　　　　　} catch (SQLException e) {//E.printstacktrace ();　}　　　　}　　}　return false;} 

Note: The code executes the SQL statement that the user logged in exactly, and the regular login and illegal SQL injection constructs will return a failure.
At the same time, as mentioned earlier, in order to validate SQL injection attacks, data validation and filtering are discarded. What if someone is not a problem solver, but a specialized sabotage? As if you had buried yourself a bomb, you never know when it will explode.
In fact, this is a matter of worry. We can create two MySQL accounts, one dedicated to SQL injection verification (SELECT permission only), and the rest for other business data . This makes it easy to isolate and secure.

GRANT USAGE on *. game1001 ' @ ' localhost ' identified by PASSWORD ' *25a2cd7698feed80089150f089755d752423a821 '; GRANT SELECT on ' db_gameweb ' "Tb_virtual_user ' to ' game1001 ' @ ' localhost ';

For example, create account game1001, which is only granted read-only access to the Tb_virtual_user table.
Such a service would allow SQL injection to exist, but these SQL injections are not offensive.

Postscript:
In fact, I am still very satisfied with the problem. I've always wanted to be able to build a similar problem and be entertaining. Just be yourself a way to fight later! With June mutual encouragement.

Public Number & Games sites:
Personal public Number: Wooden purpose H5 game world
　　
Personal Game Folio site (still under construction ...): www.mmxfgame.com, also direct IP access : http://120.26.221.54/.

Web Breakthrough Game (Riddle Webgame)--sql infused Pandora's Box