SQL INJECTION and two queries

Question

So, I read article about SQL injection and there was an example:

SELECT * FROM table_name WHERE smth = 'x'; 
UPDATE table_name SET smth ='[email protected]' WHERE user = 'admin';

Why it doesn't work? Or it is an old article and nowadays this way is nonsense? So how hackers update mysql then? Thanks.

Answer 1

It depends on how you execute the code above.

Many code languages have dedicated database communication classes which you can supply with sql parameters instead of concatenated strings.

The risk in SQL injection is forgetting to escape some of the user input in your query thus allowing malformed queries to be executed.

Answer 2

Most sites nowadays are using parametized SQL -- not inline SQL. The situation would occur above if for instance, there was parsed, inline SQL, similar to the following:

Non-Parameterized Pseudo

string sql = "SELECT * FROM table_name WHERE smth='" + UserInput + "'";
ExecuteSql(sql);

...where UserInput defines an element on the website.

Instead of adding valid data to the UserInput field, you add,

UserInput = '';DROP table_name;

...you would actually be adding new logic to the end of the query, resulting in a malicious use of the system.

Parametized statements eliminate the possibility of SQL injection, since you can't modify the structure of the query by inserting logic into the signature.

If you attempted to set the UserInput field to a malacious query, but the site used parameters in the statement, then you would be out of luck.

Parameterized Pseudo:

Adapter proc;
proc.StoredProcedure = "GetUserNames"
proc.AddParameter("@USER",UserInput);
proc.Execute();

...as @USER is now equal to the literal "'\;DROP table_name;", which the SQL will treat as a regular ol' parameter.

Answer 3

The idea behind SQL injection attacks is this:

I have a website that let's users searching for information about animals. The user types in the name of the animal, then it runs:

select * from animals where name = '$[what the user typed in]';

so if they type in sheep, the query becomes:

select * from animals where name = 'sheep';

However, what if they type in: `sheep'; drop table animals'? If I simply copy what they typed into the query and run it, I'll run:

select * from animals where name = 'sheep'; drop table animals;

which would be bad.

This kinds of attacks can still happen if the person setting up the website and database isn't careful to look for and clean up any SQL that is in something the user enters.

Answer 4

DB 101 warns ardently about SQL injection, so most developers these days are aware of it and prevent it. This is most often done by using some sort of prepared statement where your parameters are injected via a mechanism that prevents arbitrary SQL from being executed. Lazy programming can still lead to vulnerabilities and they're out there, for sure, but blind dynamic SQL building is rarer and rarer.

Answer 5

SQL injection attacks are possible when you have query "templates" and you require user input to fill in some of the query. For example, you might have a PHP script that does something like this:

<?php
$smth_value = $_POST["smth"];  // some form field
$smth_user  = $_POST["user"];  // some other form field
$smth_email = $_POST["email"]; // yet another form field

$sql1 = "SELECT * FROM table_name WHERE smth = '".$smth_value."'";  
$sql2 = "UPDATE table_name SET smth ='".$smth_email."' WHERE user = '".$smth_user."'";

mysql_query($sql1);
mysql_query($sql2);    
?>

If an individual knew the structure of my table (or figured it out somehow), they could "inject" SQL into my queries by putting SQL text into the form fields that results in my SQL variable strings looking like two valid queries separated by a semicolon. For example, someone could type into the "smth" form field something like:

';DELETE FROM table_name WHERE 1=1 OR smth='

and then $sql1 would end up looking like:

SELECT * FROM table_name WHERE smth = '';DELETE FROM table_name WHERE 1=1 OR smth=''

... and there goes all the data from table_name.

That's why there are functions in PHP like mysql_escape_string to help guard strings from these kind of attacks. If you know a variable is supposed to be a number, cast it to a number. If you have text, wrap it in a string escaping function. That's the basic idea on how to defend.