Thread: Select Where using character varying ??
<div style="background-color:"><div class="RTE">Hi Guys</div><div class="RTE"> </div><div class="RTE">I am printing
namesinto a combo box and posting the variable. I try and use the name ($Sem) in an SQL statement; WHERE name = $Sem;
butI get an error which is displayed near the end of this message. Are we able compare php string to postgresql
charactervarying?? not sure how else to do the selection</div><div class="RTE"> </div><div class="RTE"> </div><div
class="RTE">$conn= pg_Connect("host=localhost dbname=#### user=#### password=####");<br /> if (!$conn) {echo "An
databaseconnection error occurred.\n"; exit;}</div><div class="RTE"> </div><div class="RTE"> </div><div class="RTE">//
e.g.$Sem = "seminar one";</div><div class="RTE"> </div><div class="RTE">$Sem = $_POST['Seminars'];</div><div
class="RTE"> </div><divclass="RTE"> </div><div class="RTE">$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar
WHERE name= $Sem");<br /> if (!$SemNo) {echo "A query error occurred in retreiving the selected seminar's ID
<br>";/*exit;*/}<br />$Sem_No = pg_Result($Sem_No, 0);</div><div class="RTE"> </div><div class="RTE">// Error
Message</div><divclass="RTE"> </div><div class="RTE"><strong>Warning</strong>: pg_exec() [<a
href="http://www.citanalyst.com/jet/form/function.pg-exec">function.pg-exec</a>]:Query failed: ERROR: syntax error at
ornear "one" at character 54 in <b>/home/bdwilko/public_html/jet/form/insertP.php</b> on line <b>56</b><br />A query
erroroccurred in retreiving the selected seminar's ID </div><div class="RTE"> </div><div class="RTE"> </div><div
class="RTE">Table: <table><tr><td></td><td><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/redirect.php?server=%3A5432&database=bdwilko_jet&schema=public&subject=table&table=seminar"
title="Table">seminar</a></td></tr></table></div><divclass="RTE"><table><tr><th class="data"></th></tr><tr><th
class="data">Column</th><td> </td><th class="data">Type</th><th class="data">Not Null</th><th
class="data">Default</th><thclass="data" colspan="2">Actions</th><th class="data">Comment</th></tr></table></div><div
class="RTE"><table><tr><tdclass="data1">seminar_id</td><td class="data1">integer</td><td> NOT NULL
nextval('seminar_seminar_id_seq'::regclass)</td><tdclass="opbutton1"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=seminar_id&">Alter</a></td><td
class="opbutton1"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=seminar_id&">Drop</a></td><td
class="data1"></td></tr></table></div><divclass="RTE"><table><tr><td
class="data2">name</td><td> </td><td class="data2">character varying</td><td><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=name&">Alter</a></td><td
class="opbutton2"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=name&">Drop</a></td><td
class="data2"></td></tr></table></div><divclass="RTE"><table><tr><td
class="data1">address</td><td> </td><td class="data1">character varying</td><td><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=address&">Alter</a></td><td
class="opbutton1"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=address&">Drop</a></td><td
class="data1"></td></tr></table></div><divclass="RTE"><table><tr><td
class="data2">date</td><td> </td><td class="data2">date</td><td> <a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=date&">Alter</a></td><td
class="opbutton2"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=date&">Drop</a></td><td
class="data2"></td></tr></table></div><divclass="RTE"><table><tr><td
class="data1">description</td><td> </td><td class="data1">character varying <a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=description&">Alter</a></td><td
class="opbutton1"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=description&">Drop</a></td><td
class="data1"></td></tr></table></div><divclass="RTE"><table><tr><td
class="data2">notes</td><td> </td><td class="data2">character varying</td><td><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=properties&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=notes&">Alter</a></td><td
class="opbutton2"><a
href="https://www13.a2hosting.com:2083/3rdparty/phpPgAdmin/tblproperties.php?action=confirm_drop&server=%3A5432&database=bdwilko_jet&schema=public&table=seminar&column=notes&">Drop</a></td><td
class="data2"></td></tr><tr><tdclass="data1"></td></tr></table></div><div align="center" class="RTE"></div><div
class="RTE"><table><tr><tdclass="data1"></td><td class="opbutton1"></td><td
class="data1"></td></tr></table></div></div>
ben wilko wrote:
> Hi Guys
>
> I am printing names into a combo box and posting the variable. I try and use the
> name ($Sem) in an SQL statement; WHERE name = $Sem; but I get an error which is
> displayed near the end of this message. Are we able compare php string to
> postgresql character varying?? not sure how else to do the selection
>
>
> $conn = pg_Connect("host=localhost dbname=#### user=#### password=####");
> if (!$conn) {echo "An database connection error occurred.\n"; exit;}
>
>
> // e.g. $Sem = "seminar one";
>
> $Sem = $_POST['Seminars'];
>
>
> $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name = $Sem");
> if (!$SemNo) {echo "A query error occurred in retreiving the selected seminar's
> ID <br>"; /*exit;*/}
> $Sem_No = pg_Result($Sem_No, 0);
>
> // Error Message
>
> *Warning*: pg_exec() [function.pg-exec
> <http://www.citanalyst.com/jet/form/function.pg-exec>]: Query failed: ERROR:
> syntax error at or near "one" at character 54 in
> */home/bdwilko/public_html/jet/form/insertP.php* on line *56*
> A query error occurred in retreiving the selected seminar's ID
>
Make sure that you're quoting the value you want to compare. At a quick
glance, this where the error is:
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name = $Sem");
should be:
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name =
'$Sem'");
To be more accurate, you should probabley do:
$Sem = pg_escape ( $Sem );
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name =
'$Sem'");
Charley
2006/10/3, Charley Tiggs <lists@tiggs.net>:
> ben wilko wrote:
> > Hi Guys
> >
> > I am printing names into a combo box and posting the variable. I try and use the
> > name ($Sem) in an SQL statement; WHERE name = $Sem; but I get an error which is
> > displayed near the end of this message. Are we able compare php string to
> > postgresql character varying?? not sure how else to do the selection
> >
> >
> > $conn = pg_Connect("host=localhost dbname=#### user=#### password=####");
> > if (!$conn) {echo "An database connection error occurred.\n"; exit;}
> >
> >
> > // e.g. $Sem = "seminar one";
> >
> > $Sem = $_POST['Seminars'];
> >
> >
> > $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name = $Sem");
> > if (!$SemNo) {echo "A query error occurred in retreiving the selected seminar's
> > ID <br>"; /*exit;*/}
> > $Sem_No = pg_Result($Sem_No, 0);
> >
I think you should try:
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name =\"$Sem\"");
OR
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name ='$Sem'");
OR
$Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name
='".$Sem."';";);
or something like this for escape "seminar one" with " " inside the sql query
But i look the table description you still compare a integer row with
a string row
try maki the query like this:
$Sem_No = pg_Exec($conn,"SELECT name FROM seminar WHERE name =
'$Sem'");
> > // Error Message
> >
> > *Warning*: pg_exec() [function.pg-exec
> > <http://www.citanalyst.com/jet/form/function.pg-exec>]: Query failed: ERROR:
> > syntax error at or near "one" at character 54 in
> > */home/bdwilko/public_html/jet/form/insertP.php* on line *56*
> > A query error occurred in retreiving the selected seminar's ID
> >
>
> Make sure that you're quoting the value you want to compare. At a quick
> glance, this where the error is:
>
> $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name = $Sem");
>
> should be:
>
> $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name =
> '$Sem'");
>
> To be more accurate, you should probabley do:
>
> $Sem = pg_escape ( $Sem );
> $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name =
> '$Sem'");
>
> Charley
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Daniel Carrero Canales
> I think you should try: > $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name > =\"$Sem\""); Double quotes are for quoting column names, not string constants. > $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name > ='$Sem'"); Better, but all strings, especially provided by some user, should be treated by the function pg_escape_string. Consider that some user types in a form field a text like this: '; delete from seminar where ''=' When you add single quotes you get two valid queries. One of them is what you would never want to be executed ;-) And, by the way - pg_exec is a deprecated name AFAIK. The new one is pg_query. -- Ceterum censeo Internet Explorer esse delendam.
Attachment
On Tuesday 03 October 2006 16:03, Mariusz Pękala wrote:
> > I think you should try:
> > $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name
> > =\"$Sem\"");
>
> Double quotes are for quoting column names, not string constants.
>
> > $Sem_No = pg_Exec($conn,"SELECT seminar_id FROM seminar WHERE name
> > ='$Sem'");
>
> Better, but all strings, especially provided by some user, should be
> treated by the function pg_escape_string.
>
> Consider that some user types in a form field a text like this:
>
> '; delete from seminar where ''='
>
> When you add single quotes you get two valid queries. One of them is
> what you would never want to be executed ;-)
>
> And, by the way - pg_exec is a deprecated name AFAIK. The new one is
> pg_query.
probably even better would be to use pg_prepare and pg_execute.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL