Server Help

Maverick · Posted: Tue Sep 13, 2005 7:42 pm Post subject: BLOB into Database

I hope someone can help me with this.

Basically, I just want to put the banner of a player into a database for use on a webpage. However, I don't have a clue how to put this BYTE data into a BLOB in the database (in C++ ofcourse).

Code: Show/Hide

BYTE banner[96];

I tried google and searched alot of sites already, but none can provide me with a real clear answer.

Can someone point me in the right direction?
_________________

Nickname: Maverick (I changed my name!)
TWCore developer | Subspace statistics

Quan Chi2 · Posted: Tue Sep 13, 2005 9:01 pm Post subject:

I can't help you.. but the people at http://daniweb.com/ might be able to answer your question..Then again.. this is about Continuum.

Zim · Guest Offline

Even thou it's binary treat the banner as a char array/string. to keep it from breaking the query you have to search it for nuls, backslashs, and quotes(acsii 0, 34, 39, and 92). if you find one in the string use the escape sequence(a backslash infront of the character) to let mysql know it's part of the string and not the query itself.

ie. "This'is\a\messed"up'string"
would be "This\'is\\a\\messed\"up\'string"

best way to do this would be use the premade function mysql_real_escape_string().

Maverick · Posted: Wed Sep 14, 2005 8:08 am Post subject:

Can you show me some code examples?

Maverick · Posted: Wed Sep 14, 2005 9:39 am Post subject:

Currently I have

Code: Show/Hide

char* banner = (char*)p->banner;
QLClient.EncodeIntoString(banner, banner);

EncodeIntoString is a method that does the same as mysql_real_escape_string().

This I put into the database. The following output it gives on the attached banner:

Code: Show/Hide

H½½½½½½½½½½½HHHH½½½½½½½½HHHmHHHH½½½½HmmHmmHHHHHHHHmmmHHHHHHHHHHHHHHH½½½½HHHH½½½½½½½½H½½½½½½½½½½½

However, I don't think this is valid data. I can't get it to ouput to a valid image with PHP:

Code: Show/Hide

<?php
$db = mysql_connect("localhost","root","pass") or die("Could not connect to database: " . mysql_error());
mysql_select_db('bots') or die('Could not select database: ' . mysql_error());

$query = "SELECT fbBanner FROM tblonplayer WHERE fnPlayerId = 176";
$result = mysql_query($query);
$data = mysql_result($result,0,"fbBanner");

$img = imagecreatefromstring($data);
$width = imagesx($img);
$height = imagesy($img);

header("Pragma: no-cache");
header("Content-type: image/jpeg");

$out = Imagejpeg($img);
echo $out;
imagedestroy ($img);
?>

Solo Ace · Posted: Wed Sep 14, 2005 10:40 am Post subject:

I'm not sure how it was done for T3G League or player pics (maybe they just used a regular file, I don't know), but maybe you can ask NightHawk or Dustpuppy for more help.

CypherJF · Posted: Wed Sep 14, 2005 11:18 am Post subject:

What I ended up doing was converting it all to a hex string, making it a fixed length 184. Then using PHP etc pull it out of the database, where I took the header of the BMP and re-attached the remaing 96 bytes rebuilt from bottom up, so the banner was in its correct orientation.

What I would have done in my rebuild of the stats plugin (and corresponding PHP) that grabbed all this kind of stuff was to have a dedicated MySQL table for banners with unique ID's and MD5 hashes, then in the player's table, have a column with 'BannerID', so that way if someone had the same banner (ie: players with a null banner) the database wouldn't be filled up with a lot of junked bytes.
_________________
Performance is often the art of cheating carefully. - James Gosling

Maverick · Posted: Wed Sep 14, 2005 11:55 am Post subject:

Thanks for the tip, Solo, I will certainly ask them

CypherJF, can you copy/paste that code for me? (first part of your reply)

CypherJF · Posted: Wed Sep 14, 2005 12:03 pm Post subject:

I PMed it to you.

Maverick · Posted: Wed Sep 14, 2005 4:01 pm Post subject:

Ok got it working

(With the help of Cerium I must say, your code examples didn't make much clear, cypherjf

)

Here is the PHP code I use now:

Code: Show/Hide

$db = mysql_connect("localhost","root","pass") or die("Could not connect to database: " . mysql_error());
mysql_select_db('bots') or die('Could not select database: ' . mysql_error());

$playerid = 0;
if(isset($_REQUEST['playerid']))
$playerid = $_REQUEST['playerid'];

$query = "SELECT fbBanner FROM tblonplayer WHERE fnPlayerId = $playerid";
$result = mysql_query($query);
$data = mysql_result($result,0,"fbBanner");

header("Pragma: no-cache");
header("Content-type: image/bmp");

$bmpHeader2_64 = 'Qk2WBAAAAAAAADYEAAAoAAAADAAAAAgAAAABAAgAAAAAAGAAAADEDgAAxA4AAAABAAAAAQAA'
. 'AAAAADk5OQBCQkIAUlJSAFpaWgBzc3MAe3t7AISEhACMjIwAlJSUAJycnAClpaUAra2tAMbG'
. 'xgDOzs4AtbW9AK2ttQCUlJwAc3N7AGNjawDe3v8AxsbvAHNzjADOzv8AtbXvAEpKYwCEhLUA'
. 'tbX/AISE5wBCQnsAEBAhAHt7/wApKYQAGBhSAAgIzgAhKc4AUmP3AEpazgBaY5QAMUKlAAgx'
. '3gAIOf8AABhzAAQtTQAAKaUAWmN7AFKE/wA5Y84ACEK9AAAhYwCEtf8AGGvvABA5cwA5hN4A'
. 'UlpjAISMlABKjM4AKVqEAEKl/wAYjPcApdb/AGOMrQAIUoQAEHOtAAhrpQC95/8AEHu1AAhj'
. 'lACt3vcAa4ycAHO11gBChKUAKbX3ACGt7wBaa3MAMUJKAITO7wBjzv8AQq3eACm9/wBClLUA'
. 'Qr3vADHG/wAIQloACJTOAITe/wAhtecA3vf/AM7n7wCUrbUAc6W1AJzn/wBa1v8Ae5ylAFp7'
. 'hADW9/8Are//ABiEnAApxucAIZytACHn/wClra0AjJycAEJKSgCUra0AKTExAISlpQAYWloA'
. 'Mc7OADn//wAhhHsAOefWADG1pQA5xq0AOYRzAEK1lAB7zrUAMZRzAClrUgBj970AQqV7AFLe'
. 'pQBCtXsA3vfnAHOMewCcvaUAe/ecAL3nxgBrtXsAQpRSACmEOQAQORgAWt5rACFaKQBj/3MA'
. 'Ss5aADmUQgBjzmsAQq1KAK21rQC1zrUAOUI5ACExIQCl96UAOXM5ACFSIQApaykAOZQ5ABhS'
. 'GAAxSggAOUIQAFI5MQCMOTEAznNrALVSSgD/7+8A/+fnANa9vQB7a2sA987OAPfGxgBaQkIA'
. '56WlAP+1tQBrSkoA1pSUAOecnACtc3MApWtrAP+lpQDOhIQAnGNjALVzcwCEUlIAxnt7AGtC'
. 'QgCUWloAe0pKAKVjYwBjOTkASikpAHtCQgBjMTEAczk5AM5jYwD/Y2MA1lJSAL1CQgBjISEA'
. '/1JSALU5OQDeQkIAcyEhAHspMQCUWmMArYSMAIxaYwClKUoAxhBCAJQAKQCUKUoA55y1ADEY'
. 'IQCcQmMADi0/AFIAIQB7ADEArWOEANZChAC1IWMAhAA5AO+EtQD3a60ArUJzAMZKhAB7KVIA'
. 'cyFKAGMAMQCUAEoApQBSAFo5SgB7SmMA/5TOAMZrnACtQnsA3lKcAIQISgA5ACEAxmOtAGMA'
. 'SgClY5wAcwBjAHtzewAKGQoASgBKAFIAUgBzAHsAc0p7AEIxSgBCKWMAc2uEAHtzlACUc/8A'
. 'QjljAHNrlABKOZwAnIz/AHNrvQBzY+8AOSnOAEo5/wAhGHsAe3P3AEI51gApIaUA////AA==';

$bmpHeader2 = base64_decode($bmpHeader2_64);

echo $bmpHeader2.$data;

However, once the banner of the player contains any black tiles (transparent), it goes all wrong and only a part of the banner gets put into the DB. (Or nothing when top left pixel of the banner is black)

How can I fix this?

Zim · Guest Offline

If EncodeIntoString does the same as mysql_real_escape_string() then:

Code: Show/Hide

QLClient.EncodeIntoString(banner, banner);

would overflow banner on the first occurance. when using mysql_real_escape_string() you'd normally make the buffer out twice as big as the input. i dunno about EncodeIntoString but i know it doesn't like you making a pointer to the player struct's banner and then telling it to output into that same place.

here is what you'd want.
char ebanner[200];
QLClient.EncodeIntoString(&p->banner, &ebanner);

but this wouldn't work because EncodeIntoString doesn't know the length of banner and would just run to the first null(\0) and stop. don't use it... use mysql_real_escape_string().

Code: Show/Hide

char ebanner[200];

mysql_real_escape_string(&mysql, &ebanner, &p->banner, 96);

this way mysql_real_escape_string will know that if it hits a \0 before 96 bytes to \0 it and keep going. you may have to typecast p->banner.

Maverick · Posted: Wed Sep 14, 2005 4:44 pm Post subject:

thanks alot, I will try that

hm, what is the &mysql for?
nvm, got it

Maverick · Posted: Wed Sep 14, 2005 4:50 pm Post subject:

WTF
weeeeeee it works

Thanks alot Zim, CypherJF, Dustpuppy, Cerium and Solo Ace for the help

.
Now I can continue with the project

Solo Ace · Posted: Wed Sep 14, 2005 5:02 pm Post subject:

I didn't help at all, but NP.