securing mysql database query against sql injections

Question

I am a php security noob. I have two things on my website where I query my database (that only contains information about the pages that make up my website, such as title, keywords,...)

a) I dynamically create the menus. I pass a variable via the url and then scoop it up and use it in a query, like so:

User clicks on subpage.php?someid=12

I query the database:

    if(isset($_GET["someid"])) {
if (preg_match('/[0-9]+/', $_GET["someid"])) {
        $input = mysqli_real_escape_string($connect, $_GET["someid"]);
        $sql_3 = "SELECT link, title FROM pages WHERE parent_page = ".$input."";

Is this safe enough?

b) I have a little keyword search. My database table contains a text-field with keywords. The user can enter a couple of keywords into an input field and then I query the database:

if(isset($_POST["keywords"])) {

    if (preg_match('/^([a-zA-Z\-0-9]+(?:\s|$))+$/', $_POST["keywords"])) {
        $input = mysqli_real_escape_string($connect, $_POST["keywords"]);
        $sql_8 = 'SELECT id FROM pages WHERE match(keywords) against ("'.$input.'")';

Is this safe enough?

Thanks for tips and help!

Using prepared statements would already get you a long way countering sql injection.. — Naruto, May 20 '15 at 09:57
As long as you escape every user input with real_escape_string function it should be save against SQL injections. — TiMESPLiNTER, May 20 '15 at 09:58
possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Naruto, May 20 '15 at 10:01
well, how could you attack this code? I dont allow any characaters necessary for an sql query, like ; or = — user3629892, May 20 '15 at 10:24

score 1 · Accepted Answer · answered May 20 '15 at 10:33

1

Just add the following line right after the connection string.

/*Start Security Purpose*/
if (get_magic_quotes_gpc()) {
    function stripslashesGpc(&$value)
    {
        $value = stripslashes($value);
    }
    array_walk_recursive($_GET      , 'stripslashesGpc');
    array_walk_recursive($_POST     , 'stripslashesGpc');
    array_walk_recursive($_COOKIE   , 'stripslashesGpc');
    array_walk_recursive($_REQUEST  , 'stripslashesGpc');
}
//Prevent Sql Injection
$_POST = isset($_POST)?$_POST:"";
array_walk($_POST, function(&$string) use ($conn) { $string = mysqli_real_escape_string($conn, $string);});
/*End Security Purpose*/

You can look at https://github.com/jewelhuq/Simple-php-crud-project/blob/master/dbconnect.php

answered May 20 '15 at 10:33

jewelhuq

1,103
13
19

Well, thanks, but I'd really like to know how you could inject sql into my queries... could you demonstrate that? – user3629892 May 20 '15 at 11:09
Nope. Your way is ok .But you need to call the same function again & again mysqli_real_escape_string .But if you follow my strategy you dnt need to do again & again. It will escape all the input by default. – jewelhuq May 20 '15 at 19:44
aah okay, so my code is safe but just too badly written :) Thanks! – user3629892 May 21 '15 at 10:53

securing mysql database query against sql injections

1 Answers1