Using PDO to Insert Names with Apostrophe's

[alan_k]

Hello,

This has me stumped. I've looked here to see how to insert names into mysql and take into account apostrophe's in names. I've tried this:

$stmt = $link->prepare("INSERT INTO TT_Form VALUES SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status, Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");$stmt->execute(array(            ':Date_Entered' => $Date_Entered,            ':FName' => $FName,            ':LName' => $LName,            ':Dept' => $Dept,            ':Rch_Num' => $Rch_Num,            ':Rm_Number' => $Rm_Number,            ':E_Mail' => $E_Mail,            ':Prob_Cat' => $Prob_Cat,            ':Rm_Avail' => $Rm_Avail,            ':Problem' => $Problem,            ':Tkt_Status' => $Tkt_Status,            ':Assgnd_Tech' => $Assgnd_Tech,            ':TT_Num' => $TT_Num ));           $query = "INSERT INTO TT_Form (id, Date_Entered, FName, LName, Dept, Rch_Num, Rm_Number, E_Mail, Prob_Cat,             Rm_Avail, Problem, Tkt_Status, Assgnd_Tech, TT_Num) VALUES ('', '$Date_Entered', '$FName', '$LName',            '$Dept', '$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";$result = $link->query($query);

But I still cant insert a name with an apostrophe in it into MySQL table.. Everything else is ok. I understood that pdo would take care of the apostrophe problem. I have php error checking on and have no errors displaying. Does anyone see what I might be missing. Yet this code from another form works fine.

if ($FName !='' && $LName !='' && $Eqpmnt_Brwd !='') { $stmt = $link->prepare("INSERT INTO Inventory SET FName = :FName, LName = :LName, Eqpmnt_Brwd = :Eqpmnt_Brwd,                        Service_Tag = :Service_Tag, Brwd_Rsn = :Brwd_Rsn, Date_Taken = :Date_Taken, Exp_Return = :Exp_Return,  Comments = :Comments");                             $stmt->execute(array(            ':FName' => $FName,            ':LName' => $LName,            ':Eqpmnt_Brwd' => $Eqpmnt_Brwd,            ':Service_Tag' => $Service_Tag,            ':Brwd_Rsn' => $Brwd_Rsn,            ':Date_Taken' => $Date_Taken,            ':Exp_Return' => $Exp_Return,            ':Comments' => $Comments));            $query = "INSERT INTO Inventory (FName, LName, Eqpmnt_Brwd, Service_Tag, Brwd_Rsn, Date_Taken, Exp_Return, Comments, id)          VALUES('$FName','$LName','$Eqpmnt_Brwd','$Service_Tag','$Brwd_Rsn', '$Date_Taken', '$Exp_Return',  '$Comments', '')";$result=$link->query($query);

Thanks. Here is the whole file if needed..insert.php

Edited November 5, 2014 by alan_k

[alan_k]

Update Found one error. This is wrong "INSERT INTO TT_Form VALUES SET". Deleted the VALUES out of statement. Now I can add a record but it is being assigned a TT# of 0 if person has an apostrophe???? And yet the $insertid variable prints out the correct TT#. But when I look in phpMyadmin the TT# is a 0. Any help appreciatedAny help appreciated

Edited November 5, 2014 by alan_k

[justsomeguy]

What code are you using now?

[alan_k]

Here is the code:

<?phpinclude 'dbinfo.inc.php';$Date_Entered=$_POST['Date_val'];$FName=$_POST['FName_val'];$LName=$_POST['LName_val'];$Dept=$_POST['q4_department'];$Rch_Num=$_POST['q15_reach'];$Rm_Number=$_POST['q5_roomNumber'];$E_Mail=$_POST['q6_email6'];$Prob_Cat=$_POST['q11_problemCatregory'];$Rm_Avail=implode(", ",$_POST['q12_pleaseSpecify12']);$Problem=$_POST['q8_explainProblem'];$Tkt_Status=$_POST['Tkt_Status_val'];$Assgnd_Tech="Unassigned";$TT_Num=0;//Clean input to make sure it is formatted with a leading Capital letter.$FName=ucfirst($FName);$LName=ucfirst($LName);$Eqpmnt_Brwd=ucfirst($Eqpmnt_Brwd);try    {    $link=new PDO($dsn, $username,$password);    }catch (PDOException $e)    {    $error_message=$e->getMessage();    echo "<h1>Resource Unavailable. Please Contact the System Administrator</h1>";    }/*$query = "INSERT INTO TT_Form VALUES('', '$Date_Entered', '$FName', '$LName','$Dept','$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";$result = $link->query($query);*/$stmt = $link->prepare("INSERT INTO TT_Form SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status, Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");$stmt->execute(array(            ':Date_Entered' => $Date_Entered,            ':FName' => $FName,            ':LName' => $LName,            ':Dept' => $Dept,            ':Rch_Num' => $Rch_Num,            ':Rm_Number' => $Rm_Number,            ':E_Mail' => $E_Mail,            ':Prob_Cat' => $Prob_Cat,            ':Rm_Avail' => $Rm_Avail,            ':Problem' => $Problem,            ':Tkt_Status' => $Tkt_Status,            ':Assgnd_Tech' => $Assgnd_Tech,            ':TT_Num' => $TT_Num ));           $query = "INSERT INTO TT_Form (id, Date_Entered, FName, LName, Dept, Rch_Num, Rm_Number, E_Mail, Prob_Cat,             Rm_Avail, Problem, Tkt_Status, Assgnd_Tech, TT_Num) VALUES ('', '$Date_Entered', '$FName', '$LName',            '$Dept', '$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";//We use the insertId function to get the last row inserted id and add 100.$insertId = $link->lastInsertId();echo $insertId;// Simple error check to see if row got added from above INSERT statement.if ($insertId == 0) {     echo "<h2>Error in Adding Trouble Ticket. Please Contact the System Administrator.</h2>";     exit;   }else  {        $insertId = $insertId + 100;   }   $count=$link->prepare("UPDATE TT_Form SET TT_Num = id + 100 Where LName = '$LName'");$count->execute();echo '<br /n>';echo '<br /n>';echo  '<h2>Your Trouble Ticket Number Is: ' . $insertId .'</h2>'; ?>

It works except when I have an apostrophe in the name. Then the TT_Num field gets assigned a value of zero??? And the weird part is the last line $insertId prints out the correct TT_Num value? My guess is that last UPDATE staement is failing but only when an apostrophe is present.

Edited November 5, 2014 by alan_k

[justsomeguy]

You're using the wrong syntax for insert. You give it the list of fields, then the list of values, e.g.:

INSERT INTO table (field1, field2, field3) VALUES ('value1', 'value2', 'value3')

You're trying to mix insert and update syntax. Update uses SET, insert does not. And just delete your $query variables, you shouldn't be building queries that way or using those.

[alan_k]

I'm sorry but I'm a little lost. I thought I was giving list of variables then values. You're saying I should just do:

INSERT INTO TT_Form (id, Date_Entered, FName, LName, Dept, Rch_Num, Rm_Number, E_Mail, Prob_Cat,             Rm_Avail, Problem, Tkt_Status, Assgnd_Tech, TT_Num) VALUES ('', '$Date_Entered', '$FName', '$LName',            '$Dept', '$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";

without the "$query =". And I am using UPDATE with SET so please forgive a relative newcomer,I am a bit confused with what needs correcting.

[alan_k]

I went with this. It works. If anybody has a solution that works more elegantly please weigh in. I guess PDO doesnt handle apostrophes on its own?

<?phpinclude 'dbinfo.inc.php';$Date_Entered=$_POST['Date_val'];$FName=$_POST['FName_val'];$LName=$_POST['LName_val'];$Dept=$_POST['q4_department'];$Rch_Num=$_POST['q15_reach'];$Rm_Number=$_POST['q5_roomNumber'];$E_Mail=$_POST['q6_email6'];$Prob_Cat=$_POST['q11_problemCatregory'];$Rm_Avail=implode(", ",$_POST['q12_pleaseSpecify12']);$Problem=$_POST['q8_explainProblem'];$Tkt_Status=$_POST['Tkt_Status_val'];$Assgnd_Tech="Unassigned";$TT_Num=0;//Clean input to make sure it is formatted with a leading Capital letter.$FName=ucfirst($FName);$LName=ucfirst($LName);$Eqpmnt_Brwd=ucfirst($Eqpmnt_Brwd);//Check For Apostrophes$FName = addslashes($FName);$LName = addslashes($LName);try    {    $link=new PDO($dsn, $username,$password);    }catch (PDOException $e)    {    $error_message=$e->getMessage();    echo "<h1>Resource Unavailable. Please Contact the System Administrator</h1>";    }$query = "INSERT INTO TT_Form VALUES('', '$Date_Entered', '$FName', '$LName','$Dept','$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";$result = $link->query($query);//We use the insertId function to get the last row inserted id and add 100.$insertId = $link->lastInsertId();echo $insertId;// Simple error check to see if row got added from above INSERT statement.if ($insertId == 0) {     echo "<h2>Error in Adding Trouble Ticket. Please Contact the System Administrator.</h2>";     exit;   }else  {        $insertId = $insertId + 100;   }   $count=$link->prepare("UPDATE TT_Form SET TT_Num = id + 100 Where LName = '$LName'");$count->execute();echo '<br /n>';echo '<br /n>';echo  '<h2>Your Trouble Ticket Number Is: ' . $insertId .'</h2>'; ?>

[justsomeguy]

That is exactly the wrong solution, and you have removed all of the protection that you get by using PDO and prepared statements. The entire purpose is to not shove data directly into a query.

I'm sorry but I'm a little lost. I thought I was giving list of variables then values. You're saying I should just do:

No, you're not, this is your prepared statement that you should be using:

$stmt = $link->prepare("INSERT INTO TT_Form SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status, Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");

That is not the correct insert syntax. THAT is the code you need to fix, you need to completely delete the entire $query variable that you have, because it is dangerous to do that kind of thing and you are removing any protection of prepared statements when you just stick a variable in the middle of the query. That's specifically what prepared statements are there to avoid. DO NOT USE USER-SUPPLIED VARIABLES INSIDE THE QUERY. Use placeholders in prepared statements, and then pass the variables to the statement. That would have worked if you were using the correct insert syntax. You might also want to look into error handling with PDO, because by default it silently ignores query errors. You could switch to exceptions so that you at least get an exception when you have a query with incorrect syntax.

[alan_k]

Forgive me but I just want to understand this. I'm going to do:

$stmt = $link->prepare("INSERT INTO TT_Form SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status, Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");$stmt->execute(array(            ':Date_Entered' => $Date_Entered,            ':FName' => $FName,            ':LName' => $LName,            ':Dept' => $Dept,            ':Rch_Num' => $Rch_Num,            ':Rm_Number' => $Rm_Number,            ':E_Mail' => $E_Mail,            ':Prob_Cat' => $Prob_Cat,            ':Rm_Avail' => $Rm_Avail,            ':Problem' => $Problem,            ':Tkt_Status' => $Tkt_Status,            ':Assgnd_Tech' => $Assgnd_Tech,            ':TT_Num' => $TT_Num ));

Then Delete all the $query statements

$query = "INSERT INTO TT_Form VALUES('', '$Date_Entered', '$FName', '$LName','$Dept','$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";

and this which already commented out:

/*$query = "INSERT INTO TT_Form VALUES('', '$Date_Entered', '$FName', '$LName','$Dept','$Rch_Num', '$Rm_Number', '$E_Mail', '$Prob_Cat', '$Rm_Avail', '$Problem', '', '$Tkt_Status', '$Assgnd_Tech', '$TT_Num')";$result = $link->query($query);*/

and then how am I inserting into the Db. Thru a stmt->execute()? And what to do with the

$result = $link->query($query); 

Sorry for the questions but I am just having a hard time getting my head around prepared statements w/PDO. Thanks for the patience...

Edited November 6, 2014 by alan_k

[justsomeguy]

Yes, using execute will execute the prepared statement and insert the record. You need to rewrite the SQL query you're preparing though, it's not the correct insert syntax.

And what to do with the$result = $link->query($query);

Delete it. You don't want the $query variable or anything that uses it.Here's the manual page about error handling in PDO:http://php.net/manual/en/pdo.error-handling.phpYou should use exceptions. Then you can change your code to catch the exceptions and handle them rather than having PHP just quit when one happens. But even if it does just quit, at least you know there's a problem with your query instead of having the errors be ignored.

$link = new PDO($dsn, $username, $password);$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

[alan_k]

Appreciate your input and am on the road but still have two problems.

1. When I insert a record, two are created in mysql

2. if I insert a name with an apostrophe I get two plus the record has a Trouble Ticket number of zero.

I keep on looking over the code but dont see why this happening. If you have the inclination I would appreciate any input

as to what is going on.Here is my code so far:

<?phpini_set('display_errors',1);error_reporting(E_ALL);include 'dbinfo.inc.php';$Date_Entered=$_POST['Date_val'];$FName=$_POST['FName_val'];$LName=$_POST['LName_val'];$Dept=$_POST['q4_department'];$Rch_Num=$_POST['q15_reach'];$Rm_Number=$_POST['q5_roomNumber'];$E_Mail=$_POST['q6_email6'];$Prob_Cat=$_POST['q11_problemCatregory'];$Rm_Avail=implode(", ",$_POST['q12_pleaseSpecify12']);$Problem=$_POST['q8_explainProblem'];$Tkt_Status=$_POST['Tkt_Status_val'];$Assgnd_Tech="Unassigned";$TT_Num=0;//Clean input to make sure it is formatted with a leading Capital letter.$FName=ucfirst($FName);$LName=ucfirst($LName);try    {    $link=new PDO($dsn, $username,$password);    }catch (PDOException $e)    {    $error_message=$e->getMessage();    echo "<h1>Resource Unavailable. Please Contact the System Administrator</h1>";    }$stmt = $link->prepare("INSERT INTO TT_Form SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status,                                   Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");$stmt->execute(array(            ':Date_Entered' => $Date_Entered,            ':FName' => $FName,            ':LName' => $LName,            ':Dept' => $Dept,            ':Rch_Num' => $Rch_Num,            ':Rm_Number' => $Rm_Number,            ':E_Mail' => $E_Mail,            ':Prob_Cat' => $Prob_Cat,            ':Rm_Avail' => $Rm_Avail,            ':Problem' => $Problem,            ':Tkt_Status' => $Tkt_Status,            ':Assgnd_Tech' => $Assgnd_Tech,            ':TT_Num' => $TT_Num ));$stmt->execute();//We use the insertId function to get the last row inserted id and add 100.$TT_Num = $link->lastInsertId();// Simple error check to see if row got added from above INSERT statement.if ($TT_Num == 0) {     echo "<h2>Error in Adding Trouble Ticket. Please Contact the System Administrator.</h2>";     exit;   }else  {        $TT_Num = $TT_Num + 100;   }   $updt=$link->prepare("UPDATE TT_Form SET TT_Num = :TT_Num Where LName = '$LName'");$updt->execute(array(':TT_Num' => $TT_Num));echo '<br /n>';echo '<br /n>';echo  '<h2>Your Trouble Ticket Number Is: ' . $TT_Num .'</h2>'; ?>

 

[alan_k]

ok one problem solved. That "$stmt->execute();" wasn't needed. That's what you get for copying and pasting without understanding it is your doing and why. Still have the second trouble which is why I wanted to use prepared statements all along. I cant add a record with apostrophes and get the next tt number in the series. I get a zero on anything I add.

Update: In experimenting this only happens if there is an apostrophe in the Last Name Field. So I assume trouble is with line:

$updt=$link->prepare("UPDATE TT_Form SET WHERE LName = $LName");$updt->execute(array(':TT_Num' => $TT_Num));

Edited November 7, 2014 by alan_k

[Ingolme]

This is your problem:

$updt=$link->prepare("UPDATE TT_Form SET WHERE LName = $LName");

You've put a variable right in the query string. This is the sort of security vulnerability that prepared statements are supposed to prevent.

Put a placeholder where $LName is.

[alan_k]

I'm trying this by following what I searched online but not working:

$updt=$link->prepare("UPDATE TT_Form SET TT_NUM = :TT_Num WHERE LName = ?");$updt->execute(array($LName));

Also read further and tried this:

$updt=$link->prepare("UPDATE TT_Form SET TT_NUM = :TT_Num WHERE LName = :LName");$updt-> bindParam(':TT_Num', $TT_Num, PDO::PARAM_INT);$updt->bindParam(':LName', $Lname, PDO::PARAM_STR, 15);$updt->execute();

Still no good. And all records have TT# of zero...

 

 

Update 3:

Here's the code that worked. please weigh in if something should be revised:

$updt=$link->prepare("UPDATE TT_Form SET TT_NUM = :TT_Num WHERE LName = :LName");$updt-> bindValue(':TT_Num', $TT_Num, PDO::PARAM_INT);$updt->bindValue(':LName', $LName, PDO::PARAM_STR);$updt->execute();

I'm just having a hard time with these positional vs. named parameters. Anybody have a link to a lucid discussion on the topic?

FYI here is what solved the problem using PDO:

<?phpini_set('display_errors',1);error_reporting(E_ALL);include 'dbinfo.inc.php';$Date_Entered=$_POST['Date_val'];$FName=$_POST['FName_val'];$LName=$_POST['LName_val'];$Dept=$_POST['q4_department'];$Rch_Num=$_POST['q15_reach'];$Rm_Number=$_POST['q5_roomNumber'];$E_Mail=$_POST['q6_email6'];$Prob_Cat=$_POST['q11_problemCatregory'];$Rm_Avail=implode(", ",$_POST['q12_pleaseSpecify12']);$Problem=$_POST['q8_explainProblem'];$Tkt_Status=$_POST['Tkt_Status_val'];$Assgnd_Tech="Unassigned";$TT_Num=0;//Clean input to make sure it is formatted with a leading Capital letter.$FName=ucfirst($FName);$LName=ucfirst($LName);try    {    $link=new PDO($dsn, $username,$password);    }catch (PDOException $e)    {    $error_message=$e->getMessage();    echo "<h1>Resource Unavailable. Please Contact the System Administrator</h1>";    }$stmt = $link->prepare("INSERT INTO TT_Form SET Date_Entered = :Date_Entered, FName = :FName, LName = :LName, Dept =:Dept , Rch_Num = :Rch_Num,                                   Rm_Number = :Rm_Number, E_Mail = :E_Mail, Prob_Cat = :Prob_Cat, Rm_Avail = :Rm_Avail, Problem = :Problem, Tkt_Status = :Tkt_Status,                                   Assgnd_Tech = :Assgnd_Tech, TT_Num = :TT_Num");$stmt->execute(array(            ':Date_Entered' => $Date_Entered,            ':FName' => $FName,            ':LName' => $LName,            ':Dept' => $Dept,            ':Rch_Num' => $Rch_Num,            ':Rm_Number' => $Rm_Number,            ':E_Mail' => $E_Mail,            ':Prob_Cat' => $Prob_Cat,            ':Rm_Avail' => $Rm_Avail,            ':Problem' => $Problem,            ':Tkt_Status' => $Tkt_Status,            ':Assgnd_Tech' => $Assgnd_Tech,            ':TT_Num' => $TT_Num ));//We use the insertId function to get the last row inserted id and add 100.$TT_Num = $link->lastInsertId();// Simple error check to see if row got added from above INSERT statement.if ($TT_Num == 0) {     echo "<h2>Error in Adding Trouble Ticket. Please Contact the System Administrator.</h2>";     exit;   }else  {        $TT_Num = $TT_Num + 100;   }   $updt=$link->prepare("UPDATE TT_Form SET TT_NUM = :TT_Num WHERE LName = :LName");$updt->bindValue(':TT_Num', $TT_Num, PDO::PARAM_INT);$updt->bindValue(':LName', $LName, PDO::PARAM_STR);$updt->execute();echo '<br /n>';echo '<br /n>';echo  '<h2>Your Trouble Ticket Number Is: ' . $TT_Num .'</h2>'; ?>

Edited November 8, 2014 by alan_k

[Ingolme]

By "not working" what do you mean?

 

If you mean that no records are being modified then perhaps you're not giving it the right values.

[alan_k]

Thanks for your help. What was happening was the TT# field wasnt getting updated. After RTFM on bindValues I got it to work. For some reason bindParam didn't work.

[justsomeguy]

Why are you adding 100 to the ID? Why not just start that field at 100 instead of adding a value in PHP and then updating the database?