News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

SMF SQL/PHP: How to get username from id

Started by andy_barrette, July 31, 2022, 01:18:27 PM

Previous topic - Next topic

andy_barrette

I am integrating SMF login system into my external website. I am new to SQL, but need to find a username based on id, from SMF's native databases.

I'm trying to use SychO's solution here:
https://www.simplemachines.org/community/index.php?topic=574915.msg4068752#msg4068752

Here is a PHP script that I would like to be able to include() in another script as needed. It simply provides a function that would get the username corresponding to an id. In the smfre_members table, these associated column labels are member_name and id_member.

This test script will echo the current user's id and username, if they are a member. However I just get "Database Error Please try again..." when calling getUsername($curid). I can't tell if the problem is the way I'm using smcFunc or just a general SQL query syntax problem. Anyone can help me with my misunderstanding?

<?php
require_once('/home/full/path/SSI.php');

echo 
"<html><body>";

if(
$context['user']['is_guest']) echo "Hello Guest";
else{
    
$curid $user_info['id']; // For testing purposes only. In the end this will come from elsewhere
    
echo "Hello Member: ".$curid// This works because I am logged in by means not shown in this example
    
    
function getUsername($id){
        global 
$smcFunc;
        
        
$request $smcFunc['db_query']('''
         SELECT member_name
         FROM smfre_members
         WHERE id_member = {int:$userid}'
// can I just reference $id here and omit array() below?
        
array(
        
'userid' => $id,
        
)
        );
        list (
$mn) = $smcFunc['db_fetch_row']($request);
        
$smcFunc['db_free_result']($request);

        
// I couldn't understand why you would call an error when $customer_name is not empty
        // if (!empty($customer_name))
//       fatal_lang_error('noboard_entering_error', false);
        
        
if(!empty($mn)) return $mn;
        else return 
false;
    }
    
    echo 
$curid.": ".getUsername($curid); // This fails
}

echo 
"</body></html>";
?>

vbgamer45

What about just trying $user_info['name'];

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

andy_barrette

Thanks but this example is just for testing purposes. In the end I will be taking a user id from another database and will need to get the associated username.

Doug Heffernan

At a first/quick glance I see that you have used a variable to assign the id in the where clause.

This code:

WHERE id_member = {int:$userid}
You should use the value there without the variable sign.

WHERE id_member = {int:userid}

andy_barrette

That you Doug!

I appreciate the correction. I can't tell what the {datatype:varName} means except by inference. It's for casting, I guess, but I haven't found much info on it from search engines so I assume it is related to $smcFunc.

Anyway, that correction did not help. Still "Database Error...". No PHP errors logged by the way. Not sure where SQL errors would be logged in more detail.

Doug Heffernan

I will take a look at this when I get behind my laptop.

Arantor

So, your query:

        $request = $smcFunc['db_query']('', '
        SELECT member_name
        FROM smfre_members
        WHERE id_member = {int:$userid}', // can I just reference $id here and omit array() below?
        array(
        'userid' => $id,
        )
        );

{int:userid} means 'this is an integer, use the value supplied in the accompanying array whose key is userid'.

If it is not supplied, this is an error, if it is not an integer this is also an error.

Also you should be using real_name (as the user's display name) rather than member_name which is the user's sign-in name.

andy_barrette

Quote from: Arantor on July 31, 2022, 02:47:31 PMSo, your query:

        $request = $smcFunc['db_query']('', '
        SELECT member_name
        FROM smfre_members
        WHERE id_member = {int:$userid}', // can I just reference $id here and omit array() below?
        array(
        'userid' => $id,
        )
        );

{int:userid} means 'this is an integer, use the value supplied in the accompanying array whose key is userid'.

If it is not supplied, this is an error, if it is not an integer this is also an error.

Also you should be using real_name (as the user's display name) rather than member_name which is the user's sign-in name.

Thank you for the tip! You are correct. That is actually what I will need. I have users creating an image that goes to a gallery. The image database contains users' ids, but to display names next to images in the gallery I need a way of cross-checking id to member_name real_name.

Doug Heffernan

You can simplify the code by using this instead of a whole function:

global $smcFunc, $db_prefix, $user_info;

$request = $smcFunc['db_query']('', '
SELECT real_name
FROM {db_prefix}members
WHERE id_member = {int:userid}
LIMIT 1',
array(
'userid' => $user_info['id'],
)
);

list ($real_name) = $smcFunc['db_fetch_row']($request);

$smcFunc['db_free_result']($request);

Then you can echo members real names with this:

echo $real_name;

andy_barrette

Quote from: Doug Heffernan on July 31, 2022, 04:32:48 PMglobal $smcFunc, $db_prefix, $user_info;

    $request = $smcFunc['db_query']('', '
        SELECT real_name
        FROM {db_prefix}members
        WHERE id_member = {int:userid}
        LIMIT 1',
        array(
            'userid' => $user_info['id'],
        )
    );

    list ($real_name) = $smcFunc['db_fetch_row']($request);

    $smcFunc['db_free_result']($request);

Wow! This worked! Thank you! I could never understand what {db_prefix} meant, but now I have seen what $db_prefix echos (more than just "smfre_members"). I didn't realize that I should be using
global $db_prefix;. LIMIT 1 is surely good too although I wonder what I might do if I wanted to get the whole array of of id-name pairs, and how they would be formatted (associative array?)

I suppose I could do something simple like
'SELECT id_member, real_name
FROM {db_prefix}members'

Currently my function looks like this:
<?php function getUsername($id){
        global 
$smcFunc$db_prefix;
        
        
$request $smcFunc['db_query']('''
         SELECT real_name
         FROM {db_prefix}members
         WHERE id_member = {int:userid}
         LIMIT 1'
// can I just reference $id here and omit array() below?
        
array(
        
'userid' => $id,
        
)
        );
        list (
$mn) = $smcFunc['db_fetch_row']($request);
        
$smcFunc['db_free_result']($request);
        
        if(!empty(
$mn)) return $mn;
        else return 
false;
    } 
?>

Arantor

You honestly don't need $db_prefix globally; $smcFunc will sort that out for you as long as you use the proper {db_prefix} placeholder and it'll even do it in SSI pages where magic happens.

LIMIT 1 is of no matter, you're getting a single value anyway; the database is smart enough to deal with that.

Can you just brute force the value into the query? NO. Ever hear of SQL injections? That's literally how they start, with people shoving values into queries. The whole method here of separating the values being inserted with the whole {int:variable} is for security reasons so that something sanitises what's going into the query explicitly to prevent SQL injections.

If you wanted to get a list of things, you'd do it this way:

        $list_of_members = array(1, 2, 3, 4, 5, 6);
        $names = [];

        $request = $smcFunc['db_query']('', '
        SELECT id_member, real_name
        FROM {db_prefix}members
        WHERE id_member IN ({array_int:ids})
        array(
        'ids' => $list_of_members,
        )
        );

        while ($row = $smcFunc['db_fetch_assoc']($request))
        {
                $names[$row['id_member']] = $row['real_name'];
        }

        $smcFunc['db_free_result']($request);

Then you'd have $names where the key in the array is the member id, and the value is the member's name.

andy_barrette

Thank you for the information!

Quote from: Arantor on July 31, 2022, 05:04:50 PMCan you just brute force the value into the query? NO. Ever hear of SQL injections? That's literally how they start, with people shoving values into queries. The whole method here of separating the values being inserted with the whole {int:variable} is for security reasons so that something sanitises what's going into the query explicitly to prevent SQL injections.

I considered that, but it seemed to me that it would be impossible for someone to run or include my getUsername function in a way that would give them access to my internal databases.

Thank you for the example! It works. I see that I can set WHERE 1 or just leave it out to get the list too.

(and wow there is some bot creating hundreds of accounts on my forum. call the exterminator!)

Arantor

It doesn't matter about this one specific query, get into the habit of always doing it. This particular query you have relatively hard-coded inputs, but that won't always be the case, and the rules for correctly quoting data to put into a query are far more deceptive and hard to get right than to use the tools for the job that will always get it right.

andy_barrette

Quote from: Arantor on July 31, 2022, 05:47:47 PMIt doesn't matter about this one specific query, get into the habit of always doing it. This particular query you have relatively hard-coded inputs, but that won't always be the case, and the rules for correctly quoting data to put into a query are far more deceptive and hard to get right than to use the tools for the job that will always get it right.

Thanks for the tips. Seems like sound advice

Advertisement: