-3

In my program I have an html form that takes in a name as user input:

<form action ="view_process.php" role = "form" method = "post">
    ....
    <input type="text" name="name_in" class="form-control" placeholder="Name here" maxlength = "30">
    ....
</form>

and then I have php code that puts the user input into a variable

$name_in = trim($_POST['name_in']);

All of this is working, but when I issue the following query to a MySQL database,

$query = "SELECT * FROM students WHERE SID = $id_in AND name = $name_in;";

it only returns the database entry/tuple if the user inputs their name in quotes. What is causing this and how can I fix it? I realize I could write a function to add quotes to the input but I want to understand why this is happening and that does not seem like a good solution. I did some research and tried using addslashes(), stripslashes(), and turning on magic quotes, but nothing fixed it.

EDIT: I don't think this is a duplicate of: How can I prevent SQL-injection in PHP? because I am using mysqli and not prepared statements.

h7r
  • 4,944
  • 2
  • 28
  • 31
glen4096
  • 674
  • 1
  • 9
  • 19

2 Answers2

3

You'd take:

$query = "SELECT * FROM students WHERE SID = $id_in AND name = $name_in;";

And make it into prepared statements:

$db = new mysqli(...); //Look up the connection if you don't already home one.
$stmnt = $db->prepare("select a, b, c from students where sid = ? and name = ?");
$stmnt->bind_param('is',$id_in,$name_in);
$stmnt->execute();
$stmnt->bind_result($a,$b,$c);
while($stmtnt->fetch())
{
  // Do stuff with $a $b and $c
}

The question marks in the query statement stands for "something from the user goes here we don't trust". The first parameter to the bind_param function tell mysqli the types of each of the following parameters. In this case a int (i) and string (s) are used for id and the student name. But you should sanitize and validate your input even before you get to the query using input filters and whitelists/regexes. Note that we also ask for exactly the fields we want, and don't use * in the select. This is important because we need to match those to variables in the call to bind_results().

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
J. A. Streich
  • 1,683
  • 10
  • 13
-1

I believe you would like to do this

$query = "SELECT * FROM students WHERE SID = $id_in AND name = '$name_in'";

Text variables passed in SQL must be wrapped in quotes.

If the user adds "quotes" to their name. You must strip it, using database escape function when passing variables directly into SQL. http://php.net/manual/en/mysqli.real-escape-string.php

You would add this to your variable.
$name_in = $mysqli->real_escape_string(trim($_POST['name_in']));

tmarois
  • 2,424
  • 2
  • 31
  • 43