MySQL join statement with PHP mysqli prepare

Question

I have this join in a prepare statement

//validate slug
$pro = $mysqli->prepare("SELECT
      profiles.Image,
      members.URLName,
      members.DisplayName,
      profiles.Pieces,
      profiles.AboutMe,
      profiles.DA,
      profiles.TB,
      profiles.SK
    FROM `profiles`
    INNER JOIN `members`
      ON profiles.userID = members.ID
    WHERE members.URLName = ?");
$pro->bind_param('s',$_GET['urlslug']);
$pro->execute();
$pro->store_result();

It returns nothing, even when an expected input is provided. I believe this is because I can't put apostrophes around the input (unless I wanted to search for the URLSlug "?").

The query itself is fine I believe. If I run the SQL with the input 'user1' WITHOUT apostrophe's in PHPMyAdmin it tells me:

 #1054 - Unknown column 'user1' in 'where clause'

And with apostrophe's, it works

Showing rows 0 - 0 ( 1 total, Query took 0.0030 sec)

How do I get my prepare statement to include the necessary apostrophe's around the input and work?

Edit1: Output of:

var_dump($_GET['urlslug']) = string(5) "user1"

Working query straight from PHPMyAdmin:

   SELECT profiles.Image, 
        members.URLName, 
        members.DisplayName, 
        profiles.Pieces, 
        profiles.AboutMe, 
        profiles.DA, 
        profiles.TB, 
        profiles.SK
   FROM `profiles`
   INNER JOIN `members` ON profiles.userID = members.ID
   WHERE members.URLName = 'user1'

Edit2: This is not a duplicate. I'm not asking how to protect against SQL injections with prepare statements I'm saying that the following statement doesn't work without apostrophe's around 'user1' and how do I achieve that without putting apostrophe's around the "?" in my prepare statement:

SELECT profiles.Image, 
       members.URLName, 
       members.DisplayName, 
       profiles.Pieces, 
       profiles.AboutMe, 
       profiles.DA, 
       profiles.TB, 
       profiles.SK
  FROM `profiles`
  INNER JOIN `members` ON profiles.userID = members.ID
  WHERE members.URLName = user1

Comments under this answer were dwindling into pure noise. We've established that this question is not a clear duplicate, let's save comments for any additional clarification needed. And stop, you know, brow-beating the author of the question while we're at it. — Tim Post, May 27 '14 at 06:25
@TimPost you are right, it is not actually a duplicate, as it is rather not a real question that boils down to "why doesn't my code work". Yet th OP has to learn what prepared statement is and how to use it first. Without such a knowledge he's just making wild pointless guesses and unable to find the real cause. And now tell me that question "How to properly use prepared statement" is NOT a duplicate. — Your Common Sense, May 27 '14 at 06:28

score 0 · Accepted Answer · answered May 27 '14 at 06:37

all right they want this as answer.

There is nothing special with joins in prepared statements.
There is nothing wrong with having no apostrophes in prepared statements.
As long as your code doesn't produce any errors, it means code works fine.
Your code is all right and your ideas why it doesn't work are wrong.

If it works fine yet bring no results, then there is some other, less intellectual, cause. Such as

you are connecting to other database
there is no data to return
you misspell some variable or field name
you don't actually fetch results in your code
you fail to properly fetch results in your code
something else of the kind.

Thanks for your answer, I will continue to troubleshoot my PHP and outputs. — TraceRace, May 27 '14 at 07:32

score 0 · Answer 2 · answered Jan 16 '15 at 16:15

0

Try sanitizing your $_GET['urlslug'] and assigning it to a variable, then use the variable in your bind_param statement.

answered Jan 16 '15 at 16:15

brandon-estrella-dev

367
2
12

MySQL join statement with PHP mysqli prepare

2 Answers2