Issue when using $_POST['xyz'] in SELECT

[ikoniqoz]

REPOST:
The situation: Searching a small database with PHP/MySQL with DW8.0.2
1. I have a form where users can enter a name (or a portion of a name) and select which col in the db the name relates to. It POSTs the results to a seperate page to look up and display the result. The 2 form fields are name and column.
2. I have created a simple test page whereby I place the POST values for the fields into 2 vars ($name and $column)
3. I created a simple recordset:
SELECT *
FROM table3
WHERE $column LIKE '%$name%'
ORDER BY FamilyName ASC
which works perfectly. The recordset code is:

mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = "SELECT * FROM table3 WHERE $col LIKE '%$name%' ORDER BY FamilyName ASC";
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

4. So with that in mind I attempted to use the $_POST values directly in the recordset using the Add Varianbles section and removed the vars I mentioned in 2 above. I thought this would provide a more secure, elegant solution as the code created adds in all the extra security code. I finished up with a recordset SQL as:
SELECT *
FROM table3
WHERE column LIKE %name%
ORDER BY FamilyName ASC
and the record set code created is:

$name_rsConvicts = "-1";
if (isset($_POST['enteredName'])) {
$name_rsConvicts = (get_magic_quotes_gpc()) ? $_POST['enteredName'] : addslashes($_POST['enteredName']);
}
$column_rsConvicts = "-1";
if (isset($_POST['selectedColumn'])) {
$column_rsConvicts = (get_magic_quotes_gpc()) ? $_POST['selectedColumn'] : addslashes($_POST['selectedColumn']);
}
mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE %s LIKE CONCAT('%%', %s, '%%') ORDER BY FamilyName ASC", GetSQLValueString($column_rsConvicts, "text"),GetSQLValueString($name_rsConvicts, "text"));
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

In this case I dont get any results. I would really appreciate any suggestions . Thanks.

[Newsgroup_User]

WollombiWombat wrote:
> In this case I dont get any results. I would really appreciate any suggestions

Go to the following page:

http://www.adobe.com/go/b6c2ae2a

Download the Dreamweaver extension fix (the link's in the first
paragraph). Install the fix and restart Dreamweaver. It should then work
as expected.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[ikoniqoz]

David
Many thanks for your quick reply - over the weekend too !
Sadly the "Hot Fix" turned out to be a cold failure. But, here is what happened:
1. When I opened up and closed the Recordset SB the code was altered.
2. I re-opened the Recordset and clicked on the Test button and I was presented with a full listing of the data in the table. (Previously no data was returned).
3. I uploaded the file to the testing server and tested it. Same result as previously. ie: No data.
4. I then started to edit the SELECT statement and firstly I changed -
LIKE CONCAT('%%', %s, '%%')
to plain old
LIKE %s
which gave the same result as in steps 2 and 3 above.
5. I then edited the SELECT statement further from:
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE %s LIKE %s ORDER BY FamilyName ASC", GetSQLValueString($column_rsConvicts, "text"),GetSQLValueString($name_rsConvicts, "text"));

TO:

$query_rsConvicts = ("SELECT * FROM table3 WHERE $column_rsConvicts LIKE '%$name_rsConvicts%' ORDER BY FamilyName ASC");

Note that I dropped the sprintf code. And, naturally, it worked.

I am running XP Pro with MySQL 5.0.18 & PHP 5.1.1 and have magic_quotes_gpc turned on.
Could this be some further bug in DW8.0.2 Hot Fix perhaps? I cant imagine it is, but why else would the "factory" code fail ?
BTW: Do you know where to go on the Adobe site to see a list of DW related TechNotes in chrono order by DW version ?? Crickey, even finding the TechNote you referred to is a challenge as it is NOT LISTED on the DW TechNote Index page. AAaaarrrggghhhhh...

Regards,

[Newsgroup_User]

WollombiWombat wrote:
> David
> Many thanks for your quick reply - over the weekend too !
> Sadly the "Hot Fix" turned out to be a cold failure.

No, it's not a failure, but it can't fix broken code.

> Could this be some further bug in DW8.0.2 Hot Fix perhaps? I cant imagine it
> is, but why else would the "factory" code fail ?

No, it's not a bug. The bug lies in the 8.0.2 updater. Your problem is
that you left intact the buggy code inserted by 8.0.2. Instead, you
should have deleted the recordset and re-created it. It works fine with
a new recordset.

> BTW: Do you know where to go on the Adobe site to see a list of DW related
> TechNotes in chrono order by DW version ?? Crickey, even finding the TechNote
> you referred to is a challenge as it is NOT LISTED on the DW TechNote Index
> page. AAaaarrrggghhhhh...

What you suggest would be a very good idea. AFAIK, there is no such page.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[ikoniqoz]

Hi David
I must admit I felt like a goose when I read your post as I did not delete the Recordset and re-create it.

So, I recreated the recordset and again it still fails. Here is what I had when I re-created the code: (I have changed the variable from what was previously "column" to "field"

SQL:
SELECT *
FROM table3
WHERE field LIKE %name%

Code:
$name_rsConvicts = "-1";
if (isset($_POST['enteredName'])) {
$name_rsConvicts = $_POST['enteredName'];
}
$field_rsConvicts = "-1";
if (isset($_POST['selectedColumn'])) {
$field_rsConvicts = $_POST['selectedColumn'];
}
mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE %s LIKE %s", GetSQLValueString($field_rsConvicts, "text"),GetSQLValueString("%" . $name_rsConvicts . "%", "text"));
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

So, next I edited the code manually to be:

$name_rsConvicts = "-1";
if (isset($_POST['enteredName'])) {
$name_rsConvicts = $_POST['enteredName'];
}
$field_rsConvicts = "-1";
if (isset($_POST['selectedColumn'])) {
$field_rsConvicts = $_POST['selectedColumn'];
}
mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE $field_rsConvicts LIKE %s", GetSQLValueString("%" . $name_rsConvicts . "%", "text"));
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

Note that I have removed the first %s from the SELECT and replaced it with the actual variable $field_rsConvicts and have also dropped the reference to it from the rest of the sprintf(). This works fine - on my testing server which has magic_quotes_gpc set to ON.

The problem appears to be related to the code above the RS code that begins with:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
....
....
or the call to it from the sprintf() surrounding the SELECT statement.
Regards,

[ikoniqoz]

The plot thickens.....
I have carried out a bit more testing on my testing server and my production server with the working version of the page.
The testing server has magic_quotes_gpc turned on
The production server has magic_quotes_gpc turned off.

When I use a name like O'Neil, I get a correct result on my testing server.
However, on my production server I get the following error message:
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 'Neil%'' at line 1.

Now, reading from my Bible "Foundation PHP for DW8" (page 62 Point 8 which refers to magic_quotes_gpc) I see that "Dreamweaver is designed to work properly regadless of how this is set".

I created another test page and this time junked all the MM code for magic_quotes / stripslashes and tried the PHP stripslashes snippet from the same book and got the same result.

Is this a seperate issue or is it related?

[Newsgroup_User]

WollombiWombat wrote:
> When I use a name like O'Neil, I get a correct result on my testing server.
> However, on my production server I get the following error message:
> 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 'Neil%'' at line 1.

It seems as though there are two problems with your page. The first one
is that "field" isn't being correctly picked up as a variable in the
SQL. I'm not sure if it's a reserved word, but I would suggest choosing
a different name, such as "col1" or "myVar".

The other point is that something is going wrong with magic quotes. The
code created by Dreamweaver does handle them correctly. I suspect what
may have happened is that the recordset has been removed, but some old
code is causing problems.

It's a pain, but my suggestion would be to remove all PHP code from the
page and start again.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[ikoniqoz]

David
Thanks for persisting. However, I have repeatedly removed all vesitages of any PHP code on the page and started again from scratch. I also have 4 or 5 different versions of the page with differing code. Have even tried your PHP stripslashes snippet..

I considered the possibility of a reserved word as initially I used "column" as the name of a Variable in the Recordset Variables list, then changed it to "field". However, that should not matter since DW codes the name of the Variable that is added in the RS definition by appending the name of the RS to it. For example if you add a Variable in the box called "field" it becomes $field_rsConvicts in my case.

I was beginning to think there may be something screwed up in my testing server environment. So I did a test using another site that makes heavy use of Interakt's Nextensio Lists and Forms and it works fine. I will start a new site from scratch and see what happens with that using the same search method.

I am not all that concerned about the magic_quotes issue, but am really concerned that by using DW's Create RS SB I am getting code that does not work, and cant figure out why. All the PHP/MySQL code has to do is take two POST values, one being anything the user types in for as a Name of a convict or ship (or part thereof) and indicate by selecting Family Name or Ship from a Radio Group. Thus two values are sent to the file. A value the user typed in for Name and a set value from the selected Radio button which becomes the column to be searched. Now, since the value of the Radio button can be eeither "FamilyName" or "Ship", thats all the $_POST['selectedColumn'] can be which is why I dropped it from the sprintF() which in turn meant I did not have to include it in the last part of the statement. Why that happens is any body's guess.

I wont do any more with this today, however I will return to it tomorrow. Stay tuned.......