CODEWITHSUNDEEP
mysqlsecurity
Dan
Dan

Reputation: 997

MySQL Security Check

Evening all,

Before i make my site live i obviously want to ensure it's secure (or as secure as possible).

I have a search form, an opportunity for a user to upload an entry to a database, and that's about it i think.

So i just want to check what i should be doing to protect things. Firstly, my database is accessed by a dedicated user account (not admin or root), so i think i've got that part locked down.

Secondly, on all my search queries i have this sort of format:

$result = mysql_query(
"SELECT *
FROM table 
WHERE fieldname = '" . mysql_real_escape_string($country) . "' 
AND county = '" . mysql_real_escape_string($county) . "'
ORDER BY unique_id DESC");

Finally, on the $_POST fields from my submission form, i treat the variables with this BEFORE they are inserted into the database:

$variable = mysql_real_escape_string($variable);
$result = mysql_query(
"INSERT INTO table (columnone)
VALUES ($variable)";

Could anyone let me know what else i should be considering or whether this is acceptable enough?

Thanks in advance, as always, Dan

Upvotes: 1

Views: 402

Answers (3)

Matt
Matt

Reputation: 7222

Have you considered using like MYSQL PDO and bound parameters in your SQL?

http://php.net/manual/en/pdostatement.bindparam.php

My understanding is that this is considerably more secure that using mysql_real_escape_string.

Upvotes: 1

Marc B
Marc B

Reputation: 360762

The code looks fine, though you should look into using PDO prepared statements if at all possible.

Beyond that, make sure that whatever account your PHP code is using to connect to MySQL has the absolute minimum in the way of permissions. Most web-facing scripts do NOT need alter/drop/create type privileges. Most can get away with only update/insert/select/delete, and maybe even less. This way, even if something goes horribly wrong with your code-level security, a malicious user can't send you a '; drop table students -- type query (re: bobby-tables.com)

Upvotes: 2

Pekka
Pekka

Reputation: 449613

Everything you show looks fine in terms of protection against SQL injection, except for

$variable = mysql_real_escape_string($variable);
$result = mysql_query(
"INSERT INTO table (columnone)
VALUES ($variable)";

this desperately needs quotes around $variable - or as @Dan points out, a check for whether it's a number - to be secure. mysql_real_escape_string sanitizes string data only - that means, any attempt to break out of a string delimited by single or double quotes. It provides no protection if the inserted value is not surrounded by quotes.

Upvotes: 2

Related Questions