CODEWITHSUNDEEP
php
detjo
detjo

Reputation: 109

mysql_query with $_SESSION or quotes

I'm having some grief with this and would appreciate some help. I have tried the following:

$result = mysql_query("SELECT this FROM that WHERE tFirstName = $_SESSION['FirstName'] And tLastName = $_SESSION['LastName']");

but I get error "unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE".

I tried a number of ways with double and single quotes around the session variables but it always seems to find an error.

Eventually I gave up with that and set them before hand to $fn and $ln - then used that in the query:

$result = mysql_query("SELECT this FROM that WHERE tFirstName = '$fn' And tLastName = '$ln'");

This worked fine ... until I came across a user with the last name O'Reilly and now the single quote in their name breaks it!

Help! Any idea how I get around this?

Upvotes: 0

Views: 144

Answers (5)

gen_Eric
gen_Eric

Reputation: 227310

You need to escape your variables before using them in an SQL query! If you don't, bad things can happen (SQL Injection!1). Since you're using mysql_, you can do this.

$fn = mysql_real_escape_string($_SESSION['FirstName']);
$ln = mysql_real_escape_string($_SESSION['LastName']);

$result = mysql_query("SELECT this FROM that WHERE tFirstName = '$fn' And tLastName = '$ln'");

This is exactly the reason to stop using mysql_ and switch to PDO (or mysqli)! With those, you can use "prepared queries" and never have to worry about escaping again (it does it for you). Please see this page: https://www.php.net/manual/en/mysqlinfo.api.choosing.php

Once you switch, you want either $mysqli->prepare or $dbh->prepare.

1See this for info on SQL Injection: http://bobby-tables.com/

Upvotes: 2

Wesley Schleumer de Góes
Wesley Schleumer de Góes

Reputation: 6527

You can solve this using mysql_real_escape_string or using PDO.

mysql_real_escape_string:

$str = mysql_real_escape_string($str);

PDO:

 // make a connection using mysql on PDO
 $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
 // make a query using params :fn and :ln
 $stmt = $dbh->prepare("SELECT this FROM that WHERE tFirstName = :fn And tLastName = :ln");
 // bind value to these params
 // when binding params, PDO automaticaly escape it and quote it, it's the most safe way to avoid SQL Injection.
 $stmt->bindParam(':fn', $fn);
 $stmt->bindParam(':ln', $ln);
 // execute the created statement
 $stmt->execute();
 // fetch data from statement
 $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
 // dump data
 sprintf("<pre>%s</pre>", printr($data,true));

Upvotes: 0

Pankucins
Pankucins

Reputation: 1720

First off, I'll repeat myself once again and say this - you shouldn't be using PHP mysql_* functions as they are deprecated. Please consider switching to mysqli or PDO.

Now, the problem with your query is that you're not making sure that the user won't be able to inject malicious code into your database. The query won't work with the last name "O'Reilly" because of the apostrophe '. It's breaking the MySQL query.
Before inserting data into the database please use a functions like htmlspecialchars() and mysql_real_escape_string() (if you still insist on sticking with mysql_*)

Upvotes: 0

Jon Lin
Jon Lin

Reputation: 143936

You should use something like mysqli_real_escape_string on your inputs:

$fn = mysqli_real_escape_string($link,$_SESSION['FirstName']);
$ln = mysqli_real_escape_string($link,$_SESSION['LastName']);

where $link is the link identifier returned by mysqli_connect() or mysqli_init(). Then:

$result = mysqli_query($link, "SELECT this FROM that WHERE tFirstName = '$fn' And tLastName = '$ln'");

Upvotes: 0

bwoebi
bwoebi

Reputation: 23787

to escape the single quotes, use mysql_real_escape_string() before putting the variables into the query.

$result = mysql_query("SELECT this FROM that WHERE tFirstName = '".mysql_real_escape_string($fn)."' And tLastName = '".mysql_real_escape_string($ln)."'");

The correct syntax for the first query (without the escaping now...):

$result = mysql_query("SELECT this FROM that WHERE tFirstName = {$_SESSION['FirstName']} And tLastName = {$_SESSION['LastName']}");

Upvotes: 0

Related Questions