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>";
?>
What about just trying $user_info['name'];
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.
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}
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.
I will take a look at this when I get behind my laptop.
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.
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.
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;
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;
} ?>
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.
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!)
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.
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