Database security

[shail_arya]

Hello all,
I am using stripslashes followed by real escape string function on my comment section for users. Will this keep my database safe?

Thanks
Shail

[mawburn]

You should use prepared statements, like PDO.

[felgall]

@shail_arya

I am using stripslashes followed by real escape string function

That will break your data and will not keep it safe at all.

Keep the data completely separate from the SQL and forget all that escaping nonsense.

[shail_arya]

Pls suggest a good tutorial for it, itried earlier too, but nit able to understand.
I also read that if it is not applied correctly, then it will not work.
Is there anyway to check its output.

[felgall]

@shail_arya

Pls suggest a good tutorial for it

Are you using mysqli_ calls or are you using PDO?

[mawburn]

Pls suggest a good tutorial for [PDO]

Sorry I don’t know any. I mean I could probably Google you some, but that would be literally all. Now that this is in the PHP section, you’ll probably receive better response.

[shail_arya]

mysqli… I am really worried about the security of my database…
So looking for best option

[felgall]

@shail_arya

mysqli

An example of using prepare and bind to keep the SQL and data separate - the SQL is in the prepare statement and the data fields are in the bind statement making SQL injection completely impossible.

if ($p = $db->prepare('UPDATE address SET missing=?, mail_centre=?, address=?, suburb=?, state=?, postcode=?, country=?, phone=? WHERE member_number=? AND from_date=?')) {
    $p->bind_param('ssssssssss',$missing,$mail_centre,$address,$suburb,$state,$postcode,$country,$phone,$mn,$date); 
    $p->execute();
    $p->close();
}

Each ? in the prepare represents a data field in the bind with the first ? being the second value from the bind - the first parameter of the bind defines the types of all the data values.

In this example $db is the database connection.

[Lemon_Juice]

@shail_arya

I am using stripslashes followed by real escape string function on my comment section for users. Will this keep my database safe?

Yes, you will be safe. But to do it 100% correctly you should not forget about setting the character set with mysqli_set_charset() - this will make mysqli_real_escape_string() take into account the character set of your data and will avoid any pitfalls when escaping some unusual characters. For example, if your data are stored in utf8 then you run mysqli_set_charset(‘utf8’) after you connect to the db and then you use mysqli_real_escape_string() whenever you insert a string value into a query and you are safe.

Whether you first use stripslashes on not is irrelevant for security as long as you escape the values properly. However, you may need to use it for other reasons, for example if you have magic_quotes turned on (however, it is recommended to turn it off if possible).

Using prepared statements is another option like others have said.

[tpunt]

Context is also important when not sending the data separately to the query (i.e. when not using prepared statements). For example, if the OP is using an integer and doesn’t delimit it within quotes inside their query, then they’ll still be vulnerable since mysqli_real_escape_string() will only ensure that potentially harmful characters are escaped (such as backslashes, single and double quotes, etc).

//$mysqli = new MySQLi();
$escapedButStillUnsafeInteger = $mysqli->real_escape_string($_GET['id']);
$mysqli->query("SELECT * FROM relation WHERE id = {$escapedButStillUnsafeInteger}");

[Lemon_Juice]

@tpunt

Context is also important when not sending the data separately to the query (i.e. when not using prepared statements). For example, if the OP is using an integer and doesn’t delimit it within quotes inside their query, then they’ll still be vulnerable since mysqli_real_escape_string() will only ensure that potentially harmful characters are escaped (such as backslashes, single and double quotes, etc).

Good point! I think it is good practice not to use mysqli_real_escape_string() for numeric values - obviously, they mustn’t be inserted into a query straight from an unknown source so it’s best to cast them to int or float, or to check first if they are numbers (for example with is_numeric(), ctype_digit(), preg_match(), etc.).

[shail_arya]

why we used $p and if statement here. Normally we do:

$sql=“Update address SET missing=‘$post’, mail_centre=‘’$pic WHERE user=‘$u’”;
$query=mysqli_query($db,$sql);

we dont use here IF.

Also i am confused with this

@felgall

Each ? in the prepare represents a data field in the bind with the first ? being the second value from the bind - the first parameter of the bind defines the types of all the data values.

[felgall]

@shail_arya

$query=mysqli_query($db,$sql);

If you don’t use an if there then why are you bothering to set a value to $query (which I shortened to $p in my example code).

Also since you have ONE call there and the replacement has THREE you don’t want to run the second and third if the first fails so you need the if.

You should have an if testing with the single statement as well to test if the statement failed.

[shail_arya]

Is there any method to check if tgis works successfully after implementation. Means i havent used pdo evwr, now i will try to do it but how will i be able to check whether my work is correct or wrong.