[jrsmith]

I can't for the life of me figure out what I'm doing wrong here.

I had the query string echoed to see what was being sent. Here is the query:

PHP Code:

"SELECT * FROM user WHERE ID='2VJj8Khd1EOKuCXDW7N7c7URA5q9YYTE'" 

Here is the error message I see when I try to run the query:

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 ''2VJj8Khd1EOKuCXDW7N7c7URA5q9YYTE'' at line 1

There is no error connecting to the database. The table "user" exists. The field "ID" is correct. As far as I know the santax of the query is correct.

What else might be going wrong here? Somebody please help me.

[kfurlong]

Hey, can you post the code? I would like to see whats going on.
I want to see where that string is coming from.
Also what is the data type of the ID field?


Thanks,
Kevin

[jrsmith]

Hey Kevin

That string is built by a function that creates a random 32 character string that I use for ID's. What i was doing is sending a query to the database to see if a given ID already exists. The ID field is CHAR(32).

I have a function for building SELECT queries. Here is the code:

PHP Code:

//$S is what fields I want selected, $T is what table to select from, $C is the conditions or "WHERE" statements, and $O is the "ORDER BY" statement

public function Select_Query($S,$T,$C,$O){ 
    $S = filter_var($S, FILTER_SANITIZE_STRING);
    if(!$S){die("Select Query Error: Selection Failed Validation");}
    else{if($S == "ALL"){$S = "*";}$T = filter_var($T, FILTER_SANITIZE_STRING);
        if(!$T){die("Select Query Error: Table Failed Validation");}
        else{$C = filter_var($C, FILTER_SANITIZE_STRING);
            if(!$C){die("Select Query Error: Condition Failed Validation");}
            else{if($C == "NULL"){$C = "";}$O = filter_var($O, FILTER_SANITIZE_STRING);
                if(!$O){die("Select Query Error: Order Failed Validation");}
                else{if($O == "NULL"){$O = "";}
                    $Q = "SELECT ".$S." FROM ".$T; //This is where the Query string is built
                    if($C != ""){$Q .= " WHERE ".$C;}
                    if($O != ""){$Q .= " ORDER BY ".$O;}
                    echo $Q."<br><br>"; // This is where I had the Query string echoed to see what was being sent
                    $R = self::SecQuery($Q); //this is the function that actually sends the query
                    return $R;
                }
            }
        }
    }
} 

The function that sends the query is below:

PHP Code:

private function SecQuery($Q){$R = $this->cxn->query($Q) or die($this->cxn->error);} 

All of the code above is part of a class that I use to handle interaction with the database.

Any tips you can provide would be greatly helpful.

[kfurlong]

Try adding tick marks
SELECT * FROM `users` WHERE `ID`='YOUR STRING'

if that doesnt work, echo it again and run it through like PhpMyAdmin. Thats will tell you if its the way your set up the query, like in your code, or your query itself. It doesnt look like there is anything wrong with the query. However, you are setting up your columns and table with variables. which shouldnt be a problem , but I would add the ticks just to rule it out.

[jrsmith]

Hey Kevin,

Thanks for the tip. I tried adding the tick marks but I still got the same error message. I also ran the query through phpMyAdmin and the query works fine. I'm stumped here.

[kfurlong]

Can you post the calling of the function. I am getting stumped lol

[stoopkid]

If you're positive that your SQL is fine, then your PHP is broken shortly before you call the query. Check to make sure that your SQL Query is getting filled from the right $result.

PHP Code:

$R = self::SecQuery($Q); //this is the function that actually sends the query
                    return $R; 

Is your $R getting reset? That MySQL error can occur when something is left emptied, or if another column needs to be filled in.

[sqlhjalp]

Hello,

I wanted to ask a somewhat related question.

Are you using this ID as your primary key on the table ? You could be wasting memory and it could take a hit with performance versus an integer id. They are stored in memory better.

Yes having this as a reference to get an id is fine. Example being you reference this via url and hide real id. But once internal to code and sql use the integer id. Should be faster. More reading on this is in the High_Performance_MySQL book.

Could you also make this easier on you and let MySQL generate your code ?

select ENCRYPT('foobar@asdf.com','secretpassword'); Make sure table can handle a 13 char hash.


Otherwise that sql runs on my test db.