Saving text to MySQL database

What I use is the following function:

function sql_safe($tmp) {
	global $db;
	if ($db) {
		if (get_magic_quotes_gpc()) {
			$tmp = stripslashes($tmp);
		}
		$tmp = htmlentities($tmp, ENT_QUOTES, 'UTF-8');
		$tmp = rtrim((str_replace(array("\r\n","\n","\r"),'<br/>',(preg_replace('/( {2,}|^ )/em', 'str_repeat("&nbsp;", strlen("\1"))', $tmp)))),'<br/>');
		$tmp = $db->real_escape_string($tmp);
	}
	return $tmp;
}

And you call this with $textstring = sql_safe($textstring);

Besides that, I always have the tables in utf-8 and make sure to communicate with the database in utf-8 also. $db is the handle to the database:

$db = new mysqli($hostname, $username, $password, $database);

Don't know if this is the method to prevent injections, but use this for several years already, and have never seen any problem with it.

Is it that simple to prevent MySQL injections?!

Is it that simple to prevent MySQL injections?!

Well, basically that is about it what you can do. And make sure you validate user input before using it in a query.
For example:

$query = "SELECT * FROM table WHERE privilages=1 AND article=[user-entered]";

// Could be altered to:

$query = "SELECT * FROM table WHERE privilages=1 AND article=22 OR privilages=123";

To prevent this test the user input (validate) before using it in the query.

Then, how does the mysql_real_escape_string function compare to your function?

Here's the website description of it.

string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] )

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

A better example of mysql injection is this one, say you have a login page and after the user gives name and password you use this:

"SELECT * FROM users WHERE username = '".$_POST['username']."' AND password = '".$_POST['password']."'"

to check if it is a valid user with a valid password. But the above query is vulnerable to injection. Someone could login with any username like so:

"SELECT * FROM users WHERE username = 'anyuser' AND password = '' OR ''=''"

In most cases, people have magic_quotes_gpc turned on (it's the PHP default) which will add backslashes to escape all ' (single-quote), " (double quote), (backslash) and NULL characters. This is not foolproof though because there are other characters that should be escaped to be safe.

That is where mysql_real_escape_string comes in. It will escape all mysql characters that could be uses to inject additional sql into your queries. But, care needs to be taken when using this function. If magic_quotes_gpc are turned on and you use mysql_real_escape_string you will end up escaping already escaped characters. That is why I use stripslashes if magic_quotes_gpc are turned on. Now I can use mysql_real_escape_string without any problem.

In the function I use, I first test if the database is open (line 2) and if magic_quotes_gpc are turned on. The next two lines are not strictly needed. Finally I do the real_escape_string. If the database is not open, the string is returned unaltered.

In theory it should not be possible to inject sql statements in the queries. But I like to be as safe as possible and always validate user input. In the above example, you could test if the password is not longer then 12 characters and doesn't contain a space.
I always take care in validating user input that could harm security. But care should also be taken when you create your tables. If most queries can be done by using an id only, you reduce the risk of injection. For instance, after a valid user login, you know the user_id (unknown to the actual user) and from this point on you don't use the username but only the user_id. Like so:

"SELECT * FROM table WHERE user_id=$user_id"