"SELECT email FROM table WHERE name=$name"; in PHP

Question

I am facing trouble printing the details of a username from MYSQL. The quotes in WHERE name = "xxx" is the cause. This is the code:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT name, email FROM MyTable WHERE name=$name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["name"]. " - email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

How do I replace the WHERE name = $name?

the query should be `$sql = "SELECT name, email FROM MyTable WHERE name='$name'";` or `$sql = "SELECT name, email FROM MyTable WHERE name='"$name"'";` coz in SQL string should enclosed in single quotation mark — Akshay Khale, May 11 '16 at 19:43
wrap the variable `$name` is single quotes, like this: `... WHERE name='$name'";` — Rajdeep Paul, May 11 '16 at 19:43
Use prepared statements. Then you won't need to worry about quoting, or injections. — chris85, May 11 '16 at 19:44
[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 11 '16 at 19:48
@JayBlanchard Little Bobby's headed for a life of drugs and crime. So many schools now have no pupils :( — Martin, May 11 '16 at 20:03

score 0 · Answer 1 · answered May 11 '16 at 19:45

0

you are missing an '' should be: WHERE name = '$name' notice the quotes

answered May 11 '16 at 19:45

bakz

95
15

1

Try this with `$name = "O'Brian"`. – Sven May 11 '16 at 19:46
For O'Brian you can use `addslashes` function of php http://php.net/manual/en/function.addslashes.php – Akshay Khale May 11 '16 at 19:49
That's entirely the wrong function! – Sven May 11 '16 at 19:49
If you use prepared statements you don't have to worry about escaping *anything* @akshaykhale – Jay Blanchard May 11 '16 at 19:50
1

@akshaykhale please read the warning on the manual entry you linked. `Please note that use of addslashes() for database parameter escaping can be cause of security issues on most databases.` – chris85 May 11 '16 at 19:50
1

The PHP core team should hurl `addslashes` into the sun for the sake of humanity. It's always used for the wrong things. – tadman May 11 '16 at 20:44
Why do we continue to suggest syntax that will expose the developer to potential SQL injection attacks? – Jay Blanchard May 11 '16 at 21:21
i did that. and the machine processes the $name as a string. – Jun 03 '16 at 14:40
@Uttaran Wary Make sure you you are using douvle quotes, single quotes prints variables as string, only double quotes recognizes variables – bakz Jun 04 '16 at 20:52

ScaisEdge · Answer 2 · 2016-06-03T14:59:19.973

-1

I think you shoudl use this notation (previously be sure of a proper sanitize of $name for preventing potential SQL injection)

 "SELECT name, email FROM MyTable WHERE name='$name'";

edited Jun 03 '16 at 14:59

answered May 11 '16 at 19:43

ScaisEdge

129,293
10
87
97

Why do we continue to suggest syntax that will expose the developer to potential SQL injection attacks? – Jay Blanchard May 11 '16 at 20:19
this way the computer does not know to differentiate string and variable. – Jun 03 '16 at 14:41
@UttaranWary check for PHP sql sintax the sintax provided in my answer id the correct sintax for passing a string var in a sqlcommand .. and the only things eventually useful is like suggested by Jay Blanchard ..is a correct sanitize of the var for not expose to a potential SQL injection attack .. I. repeat check for PHP in SQL sintax in this case .. – ScaisEdge Jun 03 '16 at 14:58
@JayBlanchard i have update the answer with recomandition for avoiding potential SQL injection – ScaisEdge Jun 03 '16 at 15:00
It's a good recommendation, but the code still reflects the potential for problems. – Jay Blanchard Jun 03 '16 at 15:01
@JayBlanchard please provide me the right suggestion so i can add in my answer the right behavior .. (or the right notation) .. – ScaisEdge Jun 03 '16 at 15:04
You do know how to do prepared statements, right? – Jay Blanchard Jun 03 '16 at 15:16
Yes .. i know how .. do prepared statements .. – ScaisEdge Jun 03 '16 at 15:18
The problem is that many times the code provided in question is not pdo based and then a sql ->prepare( ..) is not properly an option so is (for me) useful give the correct recomandation in th context of the question .. – ScaisEdge Jun 03 '16 at 15:22

"SELECT email FROM table WHERE name=$name"; in PHP

2 Answers2