Hello,
My question is what mysqli_real_escape_string really escapes…
I used it for my one input.
$comment=mysqli_real_escape_string($db_conx, $_POST['e']);
Here i input this through a form- <script>//‘’,.‘’
When i checked database,it was completely there.
Shail
It doesn’t stop it inputting that text it stops it executing the text.
If i came to your site and your form entered straight into your mysql table without escaping what i have put in the field i can ‘inject’ sql into your script.
so you will have something like
$posted = $_POST[‘text’];
mysql_query("INSERT INTO table (sometext) VALUES(‘$posted’ ) ") or die(mysql_error()); etc
If i was feeling mean i would look to submit…
’ x’; DROP TABLE members; ’ or something like that.
If it is escaped it just goes into the database as though it was text. If it isn’t escaped it is treated as part of the sql and executes and deletes a table (if i can guess what you have). If i know you are running certain CMS’s (a quick code check would tell me that) i would know your table names.
the code becomes something like… mysql_query(“INSERT INTO table (sometext) VALUES(‘x’); DROP TABLE members;)”) or die(mysql_error()); Which is probably not what you wanted.
You could if you wanted put in a bit of code to look for nasty bits i used to use something like this as the only reason to be inserting bits of code like this is up to no good unless you are running a forum for coders (prob a bit old now and must be a newer way of doing the same thing but it works for me still)
//check for nasty surprises
if (eregi ("\\r", $_POST['s_name'].$_POST['search'])) {die("SPAM Injection Error :(");}
if (eregi ("\
", $_POST['s_name'].$_POST['search'])) {die("SPAM Injection Error :(");}
if (eregi ("Content-Transfer-Encoding", $_POST['search'].$_POST['s_name'])) {die("SPAM Injection Error. :(");}
if (eregi ("MIME-Version", $_POST['s_name'].$_POST['search'])) {die("SPAM Injection Error :(");}
if (eregi ("Content-Type", $_POST['s_name'].$_POST['search'])) {die("SPAM Injection Error :(");}
else{
//continue on and mysqli_real_escape_string and insert
hope that helps
Ok… so what i understand is that if i am using mysqli_real_escape_string function then it will keep my database safe from SQL injections during querying with database. In this way, a user is allowed to enter anything they want without any limitation on input type and on the other hand our database will be safe from attacks. So in short,mysqli_real_escape_string keeps my database % safe from attacks. Right?
Yes, although it needs to be pointed out that prepared statements are even more secure because the query and the data are sent to MySQL separately so there is no chance at all MySQL will confuse the two.
Prepared statements are recommended above mysqli_real_escape_string.
You can’t really use msqli_real_escape as a catchall defense mechanism; context here is very important. Just use prepared statements for every single DB query you make.
And anything that you do output from a user, you’ll generally want to filter it first with htmlentities(), or regex, or whatever else is appropriate for the situation
So that means, I cant depend fully on above function.
Pretty much. Just use prepared statements, and from a security perspective, you don’t need to worry about escaping input properly. You’ll still want to filter user input though
mysqli_real_escape_string() really only exists as the replacement for mysql_real_escape_string() during the first step of your conversion process from the antiquated methods to the more modern ones. The only time you’d use it would be during that conversion process when you are adding the “i” into the call and reversing the order of the parameters.
Once you get to the second step of rewriting the actual calls to make use of the extra functionality mysqli provides that function merely serves as a reminder that the conversion to using prepare and bind is not yet completed. With the SQL in the prepare statement and the data in the bind statement there is no need to escape the data to prevent it being misinterpreted as SQL as the bind statement can only contain data.
You only ever need to escape data when it could be misinterpreted as part of the code it is contained in. So when writing to a web page you’d need to escape the < and & characters as they have special meanings in HTML. You’d only do that when actually writing the HTML though as those characters will not get confused with anything else (eg. they are just normal data as far as SQL is concerned).
What you do need to do before storing data in a database is to validate it to ensure that the content is valid for the particular field. There is no one in the world with the name “DROP TABLE events” so that value should never get far enough to even consider trying to insert it into the name field in the database.
This is such an overlooked part of the process. Proper validation in itself will stonewall a decent chunk of attacks, and that isn’t even it’s purose. I honestly think a lot of people who should know better are just too lazy to write good validation routines.
well gentelmen. from where can i get a clear knowledge of statements, dont suggest php manual please
Read… but doesn’t helped me much in understanding…
can you write small examples for me for insert select and update using statements where we get entries from form with explanation…
I’m not sure what you mean…it explains how to create prepared statements using both named and unnamed placeholder
They have examples of that in the link above.
But here’s a very short example anyway…
// generic login form
$username = $_POST['username'];
$password = $_POST['password'];
$query = " SELECT username, password FROM users_table WHERE username = ? AND password = ? ";
$result = $pdo->prepare( $query );
$result->execute( array( $username, $password ) );
The ? are called placeholders.
$pdo->prepare($query); is PDO taking the query and creating a prepared statement.
$result->execute( array( $username, $password ) ); is the query being executed.
This is considered highly insecure, because you are inserting raw, unfiltered data directly into the query:
$query = " SELECT username, password FROM users_table WHERE username = $_POST['username'] AND password = $_POST['password'] ";
The old mysql_real_escape_string was a weak attempt at allowing you to do the above and feel “safe” about doing the above.
PDO prepared statements resolves that problem by precompiling the query and not allowing nasty code to be injected along the way.
why array is used here?
if i use PDO, then i need not to use any filtering?
Array is used in the execute() when you have more than one parameter in the query, even if your parameters themselves are not an array. Just the syntax that PDO uses. Note that you can pass an array directly to it as well. The above example could be written like this:
// generic login form
$data = array( 'username' => $_POST['username'], 'password' => $_POST['password'] );
$query = " SELECT username, password FROM users_table WHERE username = ? AND password = ? ";
$result = $pdo->prepare( $query );
$result->execute( $data );
But if you are not using named placeholders, you must be careful to make sure the array indexes appear in the same order as the WHERE conditions do.
As far as never needing to filter anything:
As long as you are using MySQL ver. 5.5+ and use safe character sets like utf8 / latin1 / ascii, then you’re 100% safe. Don’t get clever and try to “help” the prepared statement do it’s job.
But, this is all a very simplified generalization of a very complex topic. There are important things to consider, such as prepared statements are worthless if you do not use them correctly (or the fact that PDO with MySQL driver aren’t “true” prepared statements in the first place, they emulate one), or that even after all this, you still need to filter anything that a user inputs before you display it to the browser, or you do not properly setup your PDO configuration…and these are just some of the more mundane topics to think about.
But, this is all a very simplified generalization of a very complex topic. There are important things to consider, such as prepared statements are worthless if you do not use them correctly (or the fact that PDO with MySQL driver aren’t “true” prepared statements in the first place, they emulate one), or that even after all this, you still need to filter anything that a user inputs before you display it to the browser, or you do not properly setup your PDO configuration…and these are just some of the more mundane topics to think about. For example, there ARE attacks out there that exploit character set vulnerabilities, which leads us back to the point about properly configuring PDO. So when I say you are 100% safe, it is under the presumption that you are playing by PDO’s rules, and you have it configured properly, and you take the time to ensure that you are structuring your queries properly, being especially careful when using and assigning named placeholders.
$pdo->prepare($query); isn’t really PDO. It’s just OOP. PDO is where you specify your database whether it’d be using connect or new. Such as
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
I can simply do something like this and it wouldn’t be considered PDO.
<?php
$pdo = new mysqli("localhost", "username", "password", "database");
$query = $pdo->prepare("SELECT id, title, options, name FROM test_table");
$query->execute();
$query->bind_result($id, $title, $options, $name);
They both use the prepare statements, but when I prepare it using MySQLi, it’s not considered PDO. So $pdo->prepare($query); is just a query line that is trying to check if you queried the data or not. Basically saying that it’s a processor checking for database queries and it’s OOP since it uses ->.
Other than that, you have a choice to choose either PDO or the new MySQLi libraries. MySQLi is almost like MySQL, but MySQLi’s libraries are newer and if you don’t like PDO, you can simply practice with the new MySQLi libraries. You have the right to choose which ones you want to use. Just remember to prepare your queries. Even if you use PDO, you need to prepare your queries just like MySQLi. Even PDO has a regular query which has security vulnerability. Just because PDO is recommended, doesn’t mean it’s most suited. You can simply do the same thing with MySQLi as you could with PDO. The only thing PDO can do that MySQLi can’t is use different databases which is basically useless if the only thing you use is PHPMyAdmin.
I don’t know where you are getting that from. A quick glance at the PHP docs indicates otherwise. It is more than just “OOP”, it is how you interface with the PDO extension directly. And prepare() does just that:
“Prepares an SQL statement to be executed by the PDOStatement::execute() method”
http://php.net/manual/en/pdo.prepare.php
And there certainly are significant differences between PDO and MySQLi.
PDO not only supports many different databases (18 in all), which is a very important attribute, it also provides named parameters, and a consistent OOP-only interface. The only real advantage Mysqli over PDO is that it is slightly faster…last I saw, which was a couple years ago, PDO was generally around 3% slower on non-prepared queries and 6.5% slower on prepared queries.