Foreign MYSQL injection tutorial

(From Projectmoose 4th)
[]-[1-Simple SQL Security by Netjester.]-[]

Contact: netjester@zoite.net/irc.zoite.net

I dont know about you, oh most knowledgable of SQL users, but when I learned how to build a database driven website from varous tutorials around the Internet, I never came authentication ss the phrase "SQL Injection "... in fact, I never came authentication SS any SQL security concepts at all. the purpose of this article is to inform the SQL programmer of the dangers of SQL injection, and ways to potentially minimize the severity of any exploits in your code.

All the examples in this article are going to be using PHP. the reason for this choice is that PHP seems to be the server-side solution of choice-its free and powerful. im also using MySQL, as its all Ive ever used. I think all the examples here shoshould be applicable into SS different database servers, but I have mentioned the particle database servers affected where necessary. I assume knowledge of PHP database functions

To start with, Ill create a database and a couple of tables, so I can demonstrate how a certain security risk cocould be exploited, and to what means. rather than make a graphical representation of these tables, Ill write it in insertion SQL statements, so you can stick them on your own server and test it yourself. after all, the best way to learn is to get stuck in.

######################
# Database structure #
######################
Create database sqlsecdemo;

Create table Users (INT UserID not null AUTO_INCREMENT primary key, TEXT Username not null, TEXT Password not null );
Insert into Users SET Username = "netjester", Password = "blahblah123 ";
Insert into Users SET Username = "someoneelse", Password = "letmein ";
Insert into Users SET Username = "MrsThePlague", Password = "God ";

Create table Messages (INT MessageID not null AUTO_INCREMENT primary key, INT RelUserID not null, TEXT Message );
Insert into Messages SET RelUserID = 1, Message = "Hi everybody .";
Insert into Messages SET RelUserID = 2, Message = "What account number shall I have this large amount of money sent? ";
Insert into Messages SET RelUserID = 3, Message = "B825KM32-F please ";

Create table SomeMoreInfo (INT InfoID not null AUTO_INCREMENT primary key, TEXT Info );
Insert into SomeMoreInfo SET Info = "Some info here ";
Insert into SomeMoreInfo SET Info = "Even more information .";
Insert into SomeMoreInfo SET Info = "Information overload .";

Now we have this foundation, I can start off by showing you the most basic of attacks. Consider this PHP statement:

$ Result = pg_query ($ db, "SELECT Message FROM Messages WHERE RelUserID =". $ _ GET [uid]. "order by MessageID ");

This may appear in a script that displays all messages posted by a certain user, reached from a page with a list of users to choose from. the URL for a page which displays all of someoneelses posted messages wocould be something like sqlinjection.com/postedby.php? Uid = 2 so the full SQL statement "target = http://www.sqlinjection.com/postedby.php? Uid = 2 so the full SQL statement, with substituted variables wocould be:

SELECT Message FROM Messages WHERE RelUserID = 2 order by MessageID

Now consider this URL: sqlinjection.com/postedb..20messages000020 -- "target = http://www.sqlinjection.com/postedb...20Messages%20 --

If you were to replace the % 20 s with spaces, as % 20 is simply a URL-encoded space, you wocould see that the SQL statement sent to the server now reads like this:

SELECT Message FROM Messages WHERE RelUserID = 2; DELETE * FROM Messages -- order by MessageID

The semicolon ends the first SQL statement, and the server is now ready for another... which we provide. the -- is the SQL comment syntax-all text following that will be ignored by the server. this wocould be pinned ded by an attacker in case there are more search clses or ordering directives and such following "WHERE RelUserID = 2", which there are. if it was left out, the attackers injected SQL statement wocould probably be invalid.

MySQL is not vulnerable to this attack, as it only allows 1 statement per query, for exactly this reason. postgreSQL is vulnerable however, and probably others too, as they allow multiple SQL statements per query.

Protecting against this type of attack is fairly simple, and can be done in two ways. ideally, both ways shoshould be implemented. the first step is to add quote marks around the user-defined variable being comapared to RelUserID, like this:

$ Result = pg_query ($ db, "SELECT Message FROM Messages WHERE RelUserID =". $ _ GET [uid]. "order by MessageID ");

So, when our attacker tries the URL above, the SQL statements becomes:

SELECT Message FROM Messages WHERE RelUserID = 2; DELETE * FROM Messages -- order by MessageID

The result of this is to put all the users input into a string, which will then be compared against RelUserID. however, an attacker still has a way around this. consider the effect of an attacker simply adding a to his input, thus unquoting the string, and then again we are at his or her mercy. this is quote possible:

Sqlinjection.com/postedby.php? Uid = 2; % 20 DELETE % 20 "target = http://www.sqlinjection.com/postedby.php? Uid = 2; % 20 DELETE % 20 * % 20 FROM % 20 Messages % 20 --

Our SQL statement then becomes:

SELECT Message FROM Messages WHERE RelUserID = 2; DELETE * FROM Messages -- order by MessageID

So it looks like weve made absolutely no progress at all. they key is to add backslashes to the users input before each occcurrence of or ". this way, a user can no longer open or close a string. with this implemented, the above URL generates the SQL statement below:

SELECT Message FROM Messages WHERE RelUserID = 2; DELETE * FROM Messages -- order by MessageID

Because of the escaping of the quote, the string compared to the RelUserID is now "2; DELETE * FROM Messages --", and the attack is now neutralised once again.

Another precaution that can be taken against this kind of attack is simple data validation. as RelUserID is defined in the database as being an INT, comparing text to it seems illogical. so first of all, check that the value of $ _ GET [uid] is in fact a number, and doesnt contain anything counter t digits. if it does, you can promptly stop execution and inform the user he or she is trying to do things that perhaps they shouldnt be doing. heres a PHP function which will help you do that, and the way Id implement it in the above example.

Function nj_isInteger ($ checkString ){
If ($ checkString! = & Ereg ("^ [0-9] * $", $ checkString )){
Return true;
} Else {
Return false;
}
}

If (! Nj_isInteger ($ _ GET [uid])
Exit (User inputted UID was not an integer .);

So there we have a good basis for some slightly more advanced SQL injection attacks.

Now, imagine you wanted a page to select and display everything from a particle table. Which table it is the user decides. So it might look like this:

Mysql_query ($ db, "SELECT * FROM". $ _ GET [table]);

Here, injection can be achieved in much the same way. If the user gave the script the value "users WHERE Username = netjester", they wocould be presented with my password. Which is bad.

The way to protect against injection further than the table to select from, you again, add quotes around the value youre adding, and escape all quotes input by the user.

However, an important lesson is to be learned here. if you wish to give a user a choice such as which table will be queried, decide which tables you want to allow the user to access, and then, for example, put them in a PHP array, and use the participating array element required using a number provided by the user. that way, a user can only indirectly insert those table names you specify in