CODEWITHSUNDEEP
phpmysqlapostrophe
James Gayle
James Gayle

Reputation: 157

mysql: apostrophe not being read from varchar column in database?

i have the following table for new_supplier_request:

Id  |  ref  |  User   |  Manager (Varchar)
1      12      James     Henry O'Brien

I also have my internal_users table:

 Id  |    User_firs_name   |  user_last_name(Varchar)    
 1        Henry               O'Brien

i am using the following mysql query to give a manager permission (who is logged in) to view some content if the managers name appears for that row of data in the manager column of my new_supplier_request table

$_SESSION['username'] = internal_users.user_first_name
$_SESSION['username2'] = internal_users.user_last_name
$user = $_SESSION['username']." ".$_SESSION['username2'];

$sql34 = "select * from new_supplier_request, internal_users where new_supplier_request.status!= 'complete' and new_supplier_request.action_taken ='none' AND new_supplier_request.user_id = internal_users.user_id AND requestee_manager = '$user'";
$result = $conn->query($sql34);

For some reason this works fine if the last name does not contain an apostrophe, but because this managers last name is O'Brien for some reason when i check in my query it is failing because it is having difficulty reading the apostrophe in the name, if i change henry's last name to something like James then it works. can someone please explain what i am doing wrong? Thanks

Upvotes: 0

Views: 894

Answers (3)

Blackus
Blackus

Reputation: 7203

You should consider using PDO's prepared statements with bound parameters.

This way, your requests would be safer and a lots of problem with string parameters should be solved.

$query = $pdo->prepare('
select *
from new_supplier_request, internal_users
where new_supplier_request.status <> :status
and new_supplier_request.action_taken = :action_taken
AND new_supplier_request.user_id = internal_users.user_id
AND requestee_manager = :user
');

$query->bindValue(':status', 'complete');
$query->bindValue(':action_taken', 'none');
$query->bindValue(':user', $user);

$query->execute();

$results = $query->fetchAll();

Upvotes: 1

aergistal
aergistal

Reputation: 31219

Pass $conn->real_escape_string($user) instead of $user in the query. You should NEVER use variable values directly in the query without enforcing a type or escaping them as you will leave the code vulnerable to SQL injection attacks.

enter image description here

Upvotes: 0

prava
prava

Reputation: 3986

Check this one:

$_SESSION['username'] = mysqli_real_escape_string($conn, internal_users.user_first_name);
$_SESSION['username2'] = mysqli_real_escape_string($conn, internal_users.user_last_name);

Upvotes: 0

Related Questions