Securing SQL

Today most websites will use a combination of dynamic templates and SQL queries to load in data from the SQL DB into the empty webpage templates. This allows developers to code say, one user profile page, and then populate it with the data needed. If the SQL calls that do this population of data is enumerable or injectionable due to bad coding. Then we can abuse it to either leak information, add or modify the data set, or worse get RCE through the injection point.

INSERT	Add a row
SELECT	Read a row
UPDATE	Update a row
DELETE	Delete a row

Injection Anatomy:

Vuln SQL

Connection connection = DriverManager.getConnection();
Statement statement = connection.creatStatement();
string sql = "SELECT * FROM users WHERE email='" + email + 
    "' AND ecrypted_pass='" + password + "'";
statemnt.executeQuery(sql);

Safe SQL

Connection connection = DriverManager.getConnection();
Statement statement = connection.creatStatement();
string sql = "SELECT * FROM users WHERE email= ? AND ecrypted_pass=?";
statemnt.executeQuery(sql, email, ecrypted_pass);

Safe Ajax

$.ajax({
					url : "ajax.php?fun=login&username=" + escape(username) + "&password=" + escape(password),
					success : function(data) {
						if (data=="OK") {
							document.location.href="index.php";
							return false;
						}
						if (document.getElementById("response").firstChild) {
							document.getElementById("response").removeChild(document.getElementById("response").firstChild);
						}
						document.getElementById("response").appendChild(document.createTextNode(data));
						return false;

					},
					dataType : "text"
				});

In the above vuln-code sample we can see that the HTTP params (email, password) are directly entered into the SQL statement. The above vuln-code could be injected to with [e-mail]' -- Where the -- comments out the rest of the query and allows us to bypass authentication entirely. Further commands could be appended onto the "email" to run DB actions. Separate each statement with a semicolon and end the full "email" injection with the comment.

Advanced Injection Anatomy:

When a web server uses native backend languages the db query can also fall victim to slightly altered versions of the traditional SQL along with running the risk of having deserialization injections that may not have been a concern in simpler sql bd setups. Databases technologies like NoSQL, MongoDB, CouchDB, Cassandra, Redis, Memcached, and directories that implement LDAP should all be handled with even more care.

Preventions/Mitigations:

Parameterization: To secure the email sqli above, a dev would need to use parameterized statements as seen in the safe-sql tab. There, the '?' is a bind param where the db driver will enter in the passed param as an escaped string, evading the attack.

Object-Relational Mapping: Many server-sided frameworks allow for the use of ORM which maps the sql database to objects that a developer can reference with a typical object oriented programming reference. This abstracts away the pre written SQL calls that are securely coded and hidden in the framework so the developer does not have to be as considerate of the code or understanding of SQLi.

These ORM libraries will often let you code custom SQL queries or pass in chunks of a query along with the params. Its important to use safe practices if you follow this method.

Defense-in-Depth: This is the idea of having redundant security at various layers and stages of the data transit. This prevents a single hole from having immediate impact. Much like a multi layer hull of a ship. A basic example of this would be a WAF or IPS that can recognize attacks and stop them in-pipe. Then for data at rest, using encryption and resource separation so RCE in one spot does not mean an instant full network compromise. Principal of Least Privilege can also help reduce impact as you can have the sql calls running as an sql user and not as system for example.

SQL languages typically have preconfigured permissions that can be utilized. The DML permission would be the most common one to utilize for a working sql user with the least privilege.

Non-Verbose Errors: Having high verbosity in errors it typically a great thing if you are a developer but hackers love this also. If errors messages are being printed to the console log or are being returned and populating the template then this will be a major help to the hackers. They can extrapolate the feedback and customize their injections to ensure they hit. In some cases even if the injection always fails but an error message can provide true or false statements then the hacker can still enumerate for answers. An example would be a sign up page or login page that informs the login/sign up failure with a message like "this username is already taken" or "this user does not exist". With this the hacker can then stuff emails until they have a list of users who belong to the site. This can come in handy for later attacks and phishing.

Blind-SQL: In some cases the error may not say anything but still can be seen either through formatting changes or with sleep commands that let the user determine if the sql is being interpreted. This can be just as dangerous as a full SQLi as the user can then programmatically dump the database one character at a time.