Security of Database

Will hackers and spammers be able to access the database?

[Lowkei]

Hi all,

I'm building an email subscriptions system.

I'm thinking to store the data into MySQL db using direct connection to the database. My code is something like below.. (Don't mind the accuracy of the code. My main questions follow after the code.)

$host = "localhost";
$user = "root";
$pass = "";
$db = "profile";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

mysql_select_db($db) or die ("Unable to select database!");

$query="INSERT INTO table ( name, email) VALUES ('$_POST[name]', '$_POST[email]')";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
mysql_close($connection);

My questions:-
1) is this kind of db connections being used in practical?
2) will there be a possibility that hackers/spammers will get access to the db and steal all the information? (i'm worrying on the security of information that people subscribed to me)
3) If it's a YES for question number 2, please advice what will be the safer and better approach?

Thanks very much.

regards,
Low

[WesleyC]

This is fairly standard, but there's a few pitfalls you need to be aware of.

First, use mysql_real_escape_string on any and all $_POST, $_GET, $_COOKIE, and $_REQUEST variables that you get before you insert them into a database. Don't roll your own escaping function--it's not worth it, and will lead to security holes.

Also, if you're echoing these database values back onto the page anywhere, try using htmlentities() to escape HTML characters--otherwise, a cracker might attempt to put javascript into your database that could lead to a XSS (Cross-Site Scripting) vulnerability.

[eelixduppy]

Another thing, which isn't as big of an issue, is making sure the data you are receiving is in the correct format, and this goes for everything, not just database related applications. But, for example, if you are expected an integer from something, let's say, then you should make sure that you are getting an integer otherwise 1) throw an error or 2) cast it to an integer. For the latter case, it would look something like this:

$page = (int)$_GET['id'];

So even if this was a string, it would be cast to an int.

Lesson to be learned is that you should always know what you are dealing with before you do anything with it.

[Lowkei]

Thanks very much WesleyC and eelixduppy.

You see, I am not actually a professional programmer. Therefore my code won't be into micro-detailed.

Basically I built everything based on WYSIWYG concept which helped me a lot. However, when come to involving privacy data, i need to be sure on what i'm dealing with, especially hackers and spammers..

I hope the mysql_real_escape_string function will help.

[eelixduppy]

>> I hope the mysql_real_escape_string function will help

It certainly will. When it comes to MySQL, this escaping function is key. :)

[Lowkei]

haha..okay.

Thanks pal:)

[henry0]

quite important
I never leave my connection details (host, PW, UN etc..) in HTML/
it needs to be set for example in WWW/

[SoCal resident]

About using mysql_real_escape_string to filter content headed to a database: Another security article suggested I use addslashes to perform the same function. Is it just as effective?

[omoutop]

addslashes() should be enough for single-byte strings. For multi-byte strings though,
mysql_real_escape_string() provides better security

Anotehr issue is the character encoding.
Taken from an article:

If I want to attempt an SQL injection attack ... having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is considered to be a single character, not two. Oops, there goes the backslash.

It is strongly suggested to always use mysql_real_escape-string() and to check for data types before inserting into database

[NeilsPHP]

Fellas,
How about mysql_escape_string() ?
Is that good enough coz i can not access my db when i use mysql_real_escape_string() ?

any suggetsions ?

[NeilsPHP]

By the way,this is function i m using to check my data

<?php
function checkdata($data)
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
$data = mysql_escape_string($data);
return $data;
}
?>

[henry0]

mysql_escape_string
does not need a DB conn
to use mysql_real_escape_string [php.net]
you need to have a conn established prior using it

[SoCal resident]

Thank you for your response, omoutop. mysql_real_escape-string() it is.

[NeilsPHP]

IC.So what is the recommended practice ? check data before establishing connection or after ?
I thought its good to check that data before connection.
other than that,are both these commands similar in job they do ?

[eelixduppy]

>> other than that,are both these commands similar in job they do ?

Similar, yes. The same, no. mysql_real_escape_string takes into account the current charset for the connection that you are using so that it is safe for use. It is recommended that you use this function, especially when inserting binary data into a table.

To fix the errors you are getting from your function above, you can either have a global $link variable, or you can pass it to the function as well:

function checkdata($data)
{
global $link;
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
$data = mysql_real_escape_string($data, $link);
return $data;
} 

Or

function checkdata($data, &$link)
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
$data = mysql_escape_string($data, $link);
return $data;
} 

[NeilsPHP]

Thanks eelixduppy,
I just got to see reply.
I am only having problem checking email data entered through form as I am checking its correct format using :
---------------------------------------
<?php

if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $Email))

{proceed to next step in script}
else
{echo error}

>?
---------------------------------------
Do you know if this function will work for above check ?
Also,I am trying to use getimagesize function on images received using form for extra security (i dunno if its good idea)

$variable = array(getimagesize('$fileName'));
echo"$variable" ;

but it's not displaying anything.Why ?

thanks