Sanitizing some SQL queries

Question

What would be the easiest and most effective way to sanitize this:

$q = "SELECT * FROM `admin` " 
."WHERE `username`=' ".$_POST["username"]."' " 
."AND `passcode`=' ".$_POST["password"]."' "

Also, I am learning PHP so if you could please provide explanations, tips, suggestions, or more ways to clean up stuff to prevent SQL injections that would be most appreciated

Use PDO or mysqli and look into prepared statements for either. They both have sanitizing options that will be well documented. — Kai Qing, Apr 05 '13 at 02:53
Aside from sanitizing the query, you should consider hashing and salting your passwords rather than storing them in plain text. — Blorgbeard, Apr 05 '13 at 02:54

What have you tried · Answer 1 · 2013-04-05T03:10:30.870

I like to use $mysqli prepared statements - here is an example from the PHP site:

Explanation (see the bottom for an example using your code):

You replace the variables in the query with ? marks, and then bind the variables in at a later time.

$city = "Amersfoort";

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

Example using your code:

$q = "SELECT * FROM `admin` " 
."WHERE `username`= ? AND passcode = ?";


/* create a prepared statement */
    if ($stmt = $mysqli->prepare($q)) {

        /* bind parameters for markers */
        $stmt->bind_param("ss", $_POST['username'], $_POST['password']);

        /* execute query */
        $stmt->execute();

        /* bind result variables */
        $stmt->bind_result($district);

        /* fetch value */
        $stmt->fetch();  // This can also be while($stmt->fetch()){ Code here }

        printf("%s is in district %s\n", $city, $district);

        /* close statement */
        $stmt->close();
    }

    /* close connection */
    $mysqli->close();

I believe that example code you tried to make sucks lol. Also I would be using PDO mysqli isnt really as good. — Lemon Drop, Apr 05 '13 at 02:59
"Example using your code" *throws $q= line at the top and does nothing else* Oh yea look at me im such a troll — Lemon Drop, Apr 05 '13 at 03:05
Whatever this topic is dead anyways no point in continuing it. — Lemon Drop, Apr 05 '13 at 03:11

score 0 · Answer 2 · answered Apr 05 '13 at 02:55

If you really had to know it would be something like:

$q = "SELECT * FROM `admin` " 
."WHERE `username`=' ".mysql_real_escape_string($_POST["username"])."' " 
."AND `passcode`=' ".mysql_real_escape_string($_POST["password"])."' "

But as the others said, you should be using most likely PDO or mysqli because all mysql_ commands are deprecated.

Sanitizing some SQL queries

2 Answers2