passing TEXT data including the apostrophe

ATS16805

RE: errors which begin with: You have an error in your SQL syntax;

i thought i had a bookmark or something recorded on this topic-- in fact a discussion of my own here, but i can NOT find it.

what must we consider when submitting string data to be inserted into MySQL when that data may potentially contain an apostrophe?

i'm assuming that this is what's causing my problem, as it doesn't happen if i use a comma. i tried using addslashes() but that was DEfinitely not the solution, and neither was the opposite, stripslashes(). i'm not sure why i thought i was on the right track w/ that approach, but...

what do you recommend i read to learn how to deal with this problem once and for all? i have been avoiding using punctuation of any type in my <textarea> form fields which send long strings to a TEXT data type-- obviously this should not be the case-- i'm seriously handicapping my script. i need to fix this deficit in my PHP knowledge.

thanks for your help!

coreyp_1

addslashes() should work fine, but you can also use mysql_real_escape_string() .

if those don't work, consider posting your complete error message, along with the query you are trying to run.

ATS16805

okay... well, then maybe it's my placement of the addslashes(). i put it here:

$inquery = "INSERT INTO interest SET
F_NAME = '" . $_POST['F_NAME'] . "',
L_NAME = '" . $_POST['L_NAME'] . "',
EMAIL = '" . $_POST['EMAIL'] . "',
PHONE = '" . $_POST['PHONE'] . "',
REMARK = '" . $_POST['REMARK'] . "'";  

  $addslqur = addslashes($inquery);
  mysql_query($addslqur, $conn) or die(mysql_error());

when i have that in my code, here's the exact error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'frank\', L_NAME = \'jones\', EMAIL = \'fjones@jones.com\', PHONE = \'888797' at line 2

EDIT:
by the way, i should remark that i didn't write the "...INSERT INTO interest SET..." bit, w/ the concatenation's and the single / double quote... it was suggested to me in this thread from earlier today. i don't really understand what that's about, but it didn't give me an error, so i went w/ it. i'm considering trying my original code, which was:

$inquery = "INSERT into interest values ('', '$_POST[F_NAME]', '$_POST[L_NAME]', '$_POST[EMAIL]',
      '$_POST[PHONE]', '$_POST[REMARK]')";

coreyp_1

AHHH! 😃

it is most definitely where you are using addslashes(). consider:

$inquery = "INSERT INTO interest SET
F_NAME = '" . addslashes($_POST['F_NAME']) . "',
L_NAME = '" . addslashes($_POST['L_NAME']) . "',
EMAIL = '" . addslashes($_POST['EMAIL']) . "',
PHONE = '" . addslashes($_POST['PHONE']) . "',
REMARK = '" . addslashes($_POST['REMARK']) . "'";  

  mysql_query($inquery, $conn) or die(mysql_error());

assuming I didn't make any typos, it should work.

ATS16805

cool. thank you!

okay, before i forget to mention. does "addslashes" and/ or "stripslashes" pertain only to an instance when "magic quotes" are being used?
to the best of my knowledge, i'm NOT using magic quotes-- but i think my hosting provider might be using it-- that's possible, right? if so, will this have bearing upon the way i need to code my apps-- the code may need to be modified to work when i upload the files, vs the way i have my testing server configured? in other words, must i use precautionary code to accommodate whether the "Magic quotes" are enabled or not? (okay, that's separate question #1, and here comes another)

the INSERT query which you've used to illustrate the proper use of the addslashes() function (w/ the single, then double quotes, and concatenation), as i stated above, was suggested to me as a solution to a completely different error -- Duplicate entry '' for key 2" -- which i later discovered would be solved by fixing an erroneous IF statement where: if ($var1 == $var2 ) needed to be used, instead of my original, erroneous: if ($var1 = $var2)

so, since i discovered the true source of that original error, i decided to go back and try my original SQL INSERT query (as indicated above in thread post #3 here).

my question is this-- when i switched back to using my original query, i have NO problems-- until i use a name like in my $_POST data, such as O'leary, for example. that, of course, produces an "error in my SQL syntax". so, i went back to my original INSERT query, and added the addslashes syntax as shown here in post #4-- but apparently that is also wrong because, typing the name O'leary, causes the "error in your SQL syntax..." where the apostrophe exists.

MY QUESTION:
so, is the use of '" . addslashes($POST['L_NAME']) . "' then for the specific purpose of avoiding this error-- so that the $POST data is properly recorded with or without an apostrophe, depending on the user data entry? in other words, the syntax

 '" . addslashes($_POST['L_NAME']) . "'

is used so that the $_POST['string'] is properly passed to mySQL instead of addslashes[$post['string'] being itself inserted? that's the impression that i get from all of this-- that i must go back to using the INSERT query as illustrated by coreyp_1,

 '" . addslashes($_POST['L_NAME']) . "'

instead of my version which is sans the single quote, double quote, space, concat-- like so "INSERT into interest values ('', '$_POST['F_NAME']', ...)

coreyp_1

addslashes() lets you pass characters such as the apostraphy, quotes, etc. into a MySQL query without causing errors.

magicquotes has to do with how information is passed into a page. you can read more about it here. Be sure to read user comments, too!