I setup a page so I can practice SQL injections to get an idea of how they work, but in my page they don’t work at all. My queries are like this "SELECT fields FROM table WHERE field1 = ‘$value’ " and lets say this query is hooked up to a URL variable, if I type in ?urlvar='; SHOW TABLES; or something like that, it just escapes the ’ and nothing happens. Do SQL injections only work if the inputs aren’t wrapped in quotes? As in SELECT fields FROM table WHERE field1 = $value.
Hi FrogWarrior, welcome to the forums
Learning how to exploit vulnerabilities is a field in it’s own right.
Believe me, there are so many variations it would take great effort and time to learn even most of them.
IMHO the best use of your time and effort is to code using best practice, with security in mind right from the start, not as an afterthought.
As far as MySQL, don’t use PHP’s deprecated mysql functions. Use mysqli or PDO instead, taking advantage of the improvements eg prepared statements, bound parameters.
If you’re intent on learning how to hack, sorry but the SitePoint forums are not the place for that.
But if you want to learn how to write secure code, this is the right place.
Gudon ya, Alan!
However, the writing of secure code takes but a bit of knowledge (the simplest steps to eliminate SQL injection attacks).
Very simply, good coding of your website requires that you check every in put before submitting it to the database server. When you create a SQL query, it’s normally enclosed in double quotes (") with single quotes (') around text you associate with a database variable. Someone creating a SQL injection attack, then, would close the single quote in the input and database directives of their chosing. What a webmaster MUST do is to remove any single quote (apostrophe) from user input and mysqli_real_escape_string() does a great job of that.
Regards,
DK
Mittineague: True, exploiting is a whole skillset in itself, just being a PHP programmer doesn’t by extension mean you know about exploiting. I lost that practice app that I made, but heres one I found online:
[noparse]http://jmchilton.net/blog/?p=23[/noparse]
its an SQL injection practice tool called tweeter. I started playing with it there, and my knowledge of SQL isn’t extensive enough to know how to effectively exploit the vulnerability.
I always secure my apps as I’m building them, but to know how to do that you need an understanding of how the exploits work, otherwise you don’t know where the vulnerabilities are. I know how to secure against SQL, URL, and javascript injections, but I’m relatively new to AJAX so there are probably various vulnerabilities there that I’m unaware of. I think though to exploit javascript like that, you need to be able to enter the code into the URL field of the browser, so I suppose you could secure the site by disabling the ability to enter javascript into the URL.
dklynn: I never use mysqli_escape_string(). I just use regular expressions to filter out any dangerous characters. Problem there is that sometimes I need to let users enter quotes and stuff like that into the database so I’ve been converting them to ASCII code, but maybe the escape_string function is a simpler way to do it.
mysqli_real_escape_string () is not needed by anyone who knows how to write their database calls properly. It only exists because many people simply switched from using mysql_query to using mysqli_query (which really should only be used if there is no data to be substituted into the call at all. Where you want to pass data to the query you should use mysqli_prepare to define the query and mysqli_bind to pass the data. Keeping the data separate from the SQL like that makes SQL injection impossible and removes any need to escape the data.
First time I hear of mysqli_prepare and bind, I’ll look into this, thanks. I’ve been using mysqli_query and mysqli_multi_query for everything.
FW,
Using MySQL procedures like Stephen suggests is another excellent way to go!
www.php.net/mysqli_real_escape_string will take you to PHP’s page where it explains the options available - where the default is to encode single and double quotes.
Most webmasters don’t know/understand regular expressions (or might forget to check for the single and double quotes) but that check should be done on input data anyway. The rule of thumb to use is to check all visitor input VERY CAREFULLY before allowing it to touch your database. Any of the above can protect you and ALL may be overkill but it’s simpler than rebuilding your database. Error on the side of caution and CHECK EVERYTHING a visitor sends to your website (an ounce of prevention is worth TONS of cure!).
Regards,
DK
Some general rules to help prevent getting your database filled with junk and to prevent data being misinterpreted as code…
- VALIDATE all user input.
With PHP
a) where a function exists that can perform the validation for you use that eg. is_numeric($_POST[‘num’]),
b) if a filter exists then use that eg. filter_var($_POST[email’], FILTER_VALIDATE_EMAIL))
c) where neither of those exists then use a regular expression or whatever tests are required to ensure that the content of the field is reasonable.
If you are using a different language then use the built in validations where they are available as they are less likely to allow something invalid through as they will have been more thoroughly tested than your own code.
-
SANITIZE all other inputs (those inputs should be valid but if someone manages to tamper with the data at least you have stripped out anything that could cause problems such as injection.
-
Use different naming conventions for variables that have been validated or sanitized to the names used for fields that haven’t so that you don’t accidentally reference a “tainted” field assuming that it contains valid data. (a tainted field is one that hasn’t or potentially hasn’t been validated or sanitized yet and so can potentially contain invalid data - PHP got rid of register_globals because with that on all fields are always tainted).
-
Keep data separate from code as much as possible eg. using prepare and bind for your database calls - the code goes in the prepare statement and the data goes in the bind statement - this removes any possibility of data being misinterpreted as code.
-
Where the data can’t be kept separate from the code then ESCAPE the data before inserting it into the code eg htmlentities() before inserting data into HTML code.
You should be doing most of these even without considering security just to ensure that vyou are only saving valid data and that valid data doesn’t get confused with code. That these steps also take care of most security issues is a bonus.
Stephen,
:tup: :tup:
Others - save the prior post as it’s spot on!
Regards,
DK
Do you guys have the full official about how to protect from SQL injection? Thanks
Use prepare and bind - injection is then impossible as the data is in a separate statement from the SQL.
This has already been covered in depth earlier in this thread so if you had read the thread you would already know the answer.
some webhosting not support sqli, maybe its up tu curl or something
AFAIK the first version of PHP to have mysqli_ was version 4.1.3 released in 2002
If anyone is using a host that hasn’t upgraded in 12 years, I’d say it’s high time they changed their host.
Particularly since support for PHP 4 was dropped several years ago and so probably has quite a few exploitable security holes that have been identified by now.
There will soon be hosting that doesn’t support mysql_ calls (there may already be some) but all hosting should support mysqli_ calls.