single/double quotation problems

[HakkieDEV]

Hiya,

I've a couple of users who are called like
The I's

VBB seems to have no problem with the quotation mark inside the username.

However, when I create custom code it breaks because of the single quotation in the username.

How do I read the username with this in it correctly and later, insert it into the database again with the single/double quotation mark?

I'd rather not do a str_replace or alike.

Any thoughts?

[Dean C]

Stripslashes is the function you want :)

[HakkieDEV]

$username = addslashes($bbuserinfo['username']);

The select querys now work, but this doesn't work on the insert querys, anyone know what I should do there?

Hmmz, I've found the problem, it was because I not only used $bbuserinfo['username'], but also the lastreplyer, I didn't do addslashes on that username as well.

Thanks a bunch!

[Dean C]

Why are you inserting the username into the database? Always insert the userid, it follows the normalisation rules and gets rid of redundant data. E.g. if the username changes you'll end up having to alter two tables. If you insert the userid to get the username you just do a nice LEFT JOIN :)

[HakkieDEV]

Thats very true, I must say I haven't really thought about that before.

Having said that, I've seen the left joins in the default vbb-code, but I still don't quite understand how it works.

[Dean C]

Take a look at this article here. Hopefully it'll explain it better. In a nutshell it allows you to join two tables together with the same key and grab fields based on where the keys are the same:

http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/

[HakkieDEV]

Aah, thats alot better!

SELECT username FROM modpoints LEFT JOIN user ON modpoints.userid = user.userid;

This is very powerfull, it will reduce the number of queyes alot!

I'll play with it a bit longer, and thanks for your help mate!

[Dean C]

No problem, keep up the good work!

[Mr Blunt]

Thanks guys!!
Searching brought me here and great info!!


While I agree, adding username and/or adding a filename twice in a database is senseless for most cases .... let me explain my case and maybe some people can share their thoughts with me??


My hack's main purpose is to keep a history of file downloads.
Most importantly, my goal is to keep info that might get deleted by vB.
It logs username, userid, fileid, filename, dateline, ipaddress, and alt_ip for every download.

Example:
Private file gets leaked.
Author or a Moderator gets pissed and deletes post/file.
Result is the admin's don't have a filename or fileid anymore to research from to track down the file leaker.

Meanwhile:
The leaker gets banned, or better yet, deleted for some other reason. Now the admin's don't even have a username or userid to associate past downloads with.

Question 1) Does this sound like a good reason to save the filename and username?

So today someone ran into the Irish Syndrome, LOL.
Single Quote in username caused DB error.
After hearing that, I figure the filename error is right around the corner.

Sidenote A:
If your wondering about the rawurldecode, it's in response to a bugfix.
http://www.vbulletin.com/forum/bugs35.php?bugid=691

Sidenote B:
If your wondering about !$_GET['stc'], that is to hopefully prevent non-thumbnails from being logged .... like normal pictures in showthread .... because I find it's useless to track those since they load every thread read (when not yet cached on users pc).

Question 2) Am I SAFELY coding around username and filename correctly? This is an 'attachment_complete' hook I'm wishing to use.
if (!$_GET['stc'])
{
if ($vbulletin->userinfo['userid'])
{
$whodl_username = get_magic_quotes_gpc() ? $vbulletin->userinfo['username'] : addslashes($vbulletin->userinfo['username']);
}
$whodl_filename = is_browser('ie') ? rawurldecode($attachmentinfo['filename']) : $attachmentinfo['filename'];
$blunts_whodl_write = array(
'userid' => $vbulletin->userinfo['userid'],
'username' => $vbulletin->userinfo['userid'] ? $whodl_username : '',
'filename' => get_magic_quotes_gpc() ? $whodl_filename : addslashes($whodl_filename),
'fileid' => $vbulletin->input->clean_gpc('r', 'attachmentid', TYPE_UINT),
'ipaddress' => $vbulletin->options['logip'] ? IPADDRESS : '',
'alt_ip' => $vbulletin->options['logip'] ? ALT_IP : '',
'dateline' => TIMENOW
);
if ($blunts_whodl_write['fileid'])
{
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "blunts_whodownloaded_ip
(
userid,
username,
filename,
fileid,
ipaddress,
alt_ip,
dateline
)
VALUES
(
'" . $blunts_whodl_write['userid'] . "',
'" . $blunts_whodl_write['username'] . "',
'" . $blunts_whodl_write['filename'] . "',
'" . $blunts_whodl_write['fileid'] . "',
'" . $blunts_whodl_write['ipaddress'] . "',
'" . $blunts_whodl_write['alt_ip'] . "',
'" . $blunts_whodl_write['dateline'] . "'
)
");
}
}


Question 3) How will these things affect international users and their character systems??

Question 4) Are IP's OK as is or do they TOO need to be handled special and if so how?


OHHH, PS .... important info I suppose I should mention.

All 4 variables (username, filename, ipaddress, alt_ip) are all being saved to DB as VARCHAR's (I mean that's the data type I declare for those columns in my DB create).



EDITED (automerged) TO ADD.....
OK, I think I just figured out that vbulletin gets rid of get_magic_quotes_gpc inside class_core.php


So is this closer to what I need?
Just always addslashes no matter what?
Here's my modified top (the variables to be inserted):
if (!$_GET['stc'])
{
$whodl_filename = is_browser('ie') ? rawurldecode($attachmentinfo['filename']) : $attachmentinfo['filename'];
$blunts_whodl_write = array(
'userid' => $vbulletin->userinfo['userid'],
'username' => $vbulletin->userinfo['userid'] ? addslashes($vbulletin->userinfo['username']) : '',
'filename' => addslashes($whodl_filename),
'fileid' => $vbulletin->input->clean_gpc('r', 'attachmentid', TYPE_UINT),
'ipaddress' => $vbulletin->options['logip'] ? IPADDRESS : '',
'alt_ip' => $vbulletin->options['logip'] ? ALT_IP : '',
'dateline' => TIMENOW
);
.......and then my db_write to insert them stayed the same.

I did make an O'reilly user and this last edit appears to be working so far.
I downloaded a couple files and phpmyadmin is reporting that exact name in the database for my downloads. How come there's no slashes being shown to me when inspecting phpmyadmin? I mean the name is there, and it didn't kick an error this time SO OBVIOUSLY the addslashes "did something to get the data in there" ... I guess I just don't understand where the slashes went, LOL.

[Mr Blunt]

Just a follow-up....

MarcoH64 informed me of the following:
Addslashes is depreciated in 3.5, please use $vbulletin->db->escape_string instead .... Basicly you should sanitize all 'untrusted' (ie provided by outside sources like user input) variables used in queries."


So for anyone interested or searching like I was, here's what my previous code eventually turned into.

$blunt = array(
'wasstc' => $vbulletin->input->clean_gpc('r', 'stc', TYPE_UINT),
'fileid' => $vbulletin->input->clean_gpc('r', 'attachmentid', TYPE_UINT),
'userid' => ($vbulletin->userinfo['userid']) ? $vbulletin->userinfo['userid'] : 0,
'username' => ($vbulletin->userinfo['userid'] AND ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logusernames'])) ? $vbulletin->userinfo['username'] : '',
'filename' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logfilenames']) ? $attachmentinfo['filename'] : '',
'dateline' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logdatelines']) ? TIMENOW : 0,
'ipaddress' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logipaddress']) ? IPADDRESS : '',
'alt_ip' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logaltips']) ? ALT_IP : '',
'logguests' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logguests']) ? 1 : 0,
'logisactive' => ($vbulletin->options['blunts_whodl_logwhat'] & $vbulletin->bf_misc['bluntswhodloptions']['logisactive']) ? 1 : 0
);

if ((!$blunt['wasstc'] AND $blunt['fileid'] AND $blunt['logisactive']) AND ($blunt['userid'] OR $blunt['logguests']))
{
$db->query_write("INSERT INTO " . TABLE_PREFIX . "blunts_whodownloaded_ip (userid, username, filename, fileid, ipaddress, alt_ip, dateline)
VALUES ('" . $vbulletin->db->escape_string($blunt['userid']) . "',
'" . $vbulletin->db->escape_string($blunt['username']) . "',
'" . $vbulletin->db->escape_string($blunt['filename']) . "',
'" . $vbulletin->db->escape_string($blunt['fileid']) . "',
'" . $vbulletin->db->escape_string($blunt['ipaddress']) . "',
'" . $vbulletin->db->escape_string($blunt['alt_ip']) . "',
'" . $vbulletin->db->escape_string($blunt['dateline']) . "')
");
}
unset($blunt);

Don't mind all the "options" stuff as that was merely spice that I added to my hack to put some on/off switches inside the AdminCP options. The relevant parts are what you see in the query where "escape_string" is used to clean the variable before insertion to the database.

[Marco van Herwaarden]

When inserting into the database you should always sanitize 'untrustred' values. It don't mean that you always need to use escape_string.

As a rule of thumb:
- Integer values:
Will be mostly already have been sanitized by $vbulletin->input->clean_array_gpc('r', array('my_integer'=> TYPE_INT));or something like that, so they don't need to be sanitized anymore. Otherwise sanitize them by using intval($my_integer)

- Character string variables will 99% of the time need to go through escape_string when used in a query.