9

I've read about SQL injection so I tried it with my site and of course it worked.. I know that the solution is parameterized queries and I also know that there are a lot of examples out there but none of them mentions the part where we're connecting to the database. So here's a part of my login page's PHP code:

$userName = $_POST["username"];
$userPass = $_POST["password"];

$query = "SELECT * FROM users WHERE username = '$userName' AND password = '$userPass'";

$result = mysqli_query($dbc, $query); //$dbc is for MySQL connection: $dbc = @mysqli_connect($dbhost, $dbuser, $dbpass, $db)

$row = mysqli_fetch_array($result);

if(!$row){
    echo "No existing user or wrong password.";
}

I've been looking for the solution for a long time but I just could not figure out how I could get it work in a parameterized way. Could you please help me how I should complete my code to prevent SQL injection?

Márk Végh
  • 93
  • 1
  • 1
  • 5
  • 1
    This is explained in detail here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Guido Apr 01 '16 at 22:30
  • take a look at how to make prepare statements in PHP which will prevent sql injection: http://www.w3schools.com/php/php_mysql_prepared_statements.asp – Erick Apr 01 '16 at 22:31

2 Answers2

14

Here you go

$stmt = mysqli_prepare($dbc, "SELECT * FROM users WHERE username = ? AND password = ?");
mysqli_stmt_bind_param($stmt, "s", $userName);
mysqli_stmt_bind_param($stmt, "s", $userPass);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);

Documentation

As side note i would reccomend to encrypt your password or better use hash for security, it's not good to store password as plain text

Fabio
  • 23,183
  • 12
  • 55
  • 64
  • Thank you for your answer. I copied it into my code, now it's working with the correct datas but it still accepts the '' or '=' thing :/ – Márk Végh Apr 01 '16 at 23:03
  • 2
    @MárkVégh, of course it still accepts it - it is there in the input. However, it cannot be executed! That is the point of prepared statements. The data in the placeholders are treated as text only. – Ryan Vincent Apr 02 '16 at 06:39
  • Oh I see, but what can I do to prevent this kind of injection? Now anybody could log in with just typing it into the username and password field :/ – Márk Végh Apr 02 '16 at 11:17
  • You are protected to any kind of injections – Fabio Apr 02 '16 at 12:32
  • 2
    @MárkVégh, no, the username and password supplied must match with a record in the database. What will happen is that nothing will be returned by the query as the username and password supplied will not match any record. Remember, they are just text strings and don't have any other meaning. The point is that the only way of matching a record is to supply the correct username and password. It is not possible to modify the query with anything supplied as input text. – Ryan Vincent Apr 02 '16 at 12:49
  • 2
    @MárkVégh, prepared queries are _not text substitution_. What happens: 1) The `prepare` step parses the sql completely. It cannot be changed later. The placeholders mark where variables holding the text values will be given later. 2) The `bind` tell the database where to look for the values. 3) `Execute` runs the query using the values in the variables. – Ryan Vincent Apr 02 '16 at 13:04
  • Regarding types for `mysqli_stmt_bind_param`; `s`=string, `i`=integer, `d`=double, `b`=blob. For DateTime values, format as string then use `s`. For boolean/bit values, cast to int then use `i`. – JohnLBevan Mar 05 '22 at 09:54
-1

use:

$userPass = mysqli_real_escape_string($mysqli,$_POST["password"]);

This block the '' or '=' thing thing :) where $mysqli is your connection string ofc.

user3744959
  • 47
  • 7