Two questions about my website and MySQL

[MythicFrost]

Hi,

I have a program that checks if its up-to-date when it starts up,
I figure I need an item in my MySQL database called "version" with a value of 1.00.

Now, I've never done anything like this before. I wonder, am I supposed to just go and manually edit and increase that version number when I release an update for the program, or is there some better or proper way to do it?

My second question is, how do I go about storing an image I want the program to download later?
Do I store it in the MySQL database, or on the website? (this isn't an image you'll see on my actual website)

Kind Regards

 

[angelwatt]

You'll probably need to update the version number manually since it couldn't auto-increment unless you will only make an increase of 1 or 0.1. Depending on how you're doing the coding, you could potentially have something else update the version as you update your code, but having no idea how you're doing your coding I have no suggestions on that.

As for storing images; Two common approaches I'm aware of are to either store the images in a directory and have the DB store the path to them, or to use an algorithm like Base64 that encodes a binary file (like an image) into a ascii-string that's safe to store in a DB. Tutorials can easily be found for either of these techniques to see which one will best suit your needs.

 

[jpyc7]

I just want to add that you can store images as binary strings (BLOB). However, you may prefer the Base64 encoding into ASCII if the mysql client program/language doesn't handle binary strings.

 

[MythicFrost]

Thanks guys,

You'll probably need to update the version number manually since it couldn't auto-increment unless you will only make an increase of 1 or 0.1. Depending on how you're doing the coding, you could potentially have something else update the version as you update your code, but having no idea how you're doing your coding I have no suggestions on that.

I plan to update my program when I find bugs etc... or new features. I'll upload it to the website, change a few things to say it's the latest version.

And then I'll go to my database and just do a 0.01 increase on that value, so my program will know there is a newer one available and let them know.

Sounds simple enough, thanks!

As for storing images; Two common approaches I'm aware of are to either store the images in a directory and have the DB store the path to them, or to use an algorithm like Base64 that encodes a binary file (like an image) into a ascii-string that's safe to store in a DB. Tutorials can easily be found for either of these techniques to see which one will best suit your needs.

What if I always know the name of the image from inside my program, IE if its just called primary_image.jpg, can I access that from my program without storing the value in the DB?

 

[jpyc7]

What if I always know the name of the image from inside my program, IE if its just called primary_image.jpg, can I access that from my program without storing the value in the DB?

If the image filename (and path) is hard-coded in your program, then I agree you don't need to store the value in the DB. Putting it in the DB is just a way for you to allow it to change dynamically. So you could simply change the contents of the file instead of the path to a different file.

I think the trade-off is between how flexible you want the image download to be and how long it takes you to implement. Clearly, hard-coding the name gets you something working faster.

 

[NoNameBrand]

By 'application', do you mean a Mac app?

If so, are you using Sparkle for version checking/updating? There's documentation for setting up the server side. If you're not using Sparkle, look into it.

If some other kind of app, why do you need to store the latest version string in a DB? Why not just have text file on the webserver called 'version' and update that?

 

[MythicFrost]

If some other kind of app, why do you need to store the latest version string in a DB? Why not just have text file on the webserver called 'version' and update that?

Could I do that? Have a text file called version.txt with 1.00 written in it?
I don't know how I "connect" to the web server? Do I just try and read the file at www.example.com/currentversion.txt??

By 'application', do you mean a Mac app?

Windows app, actually

If the image filename (and path) is hard-coded in your program, then I agree you don't need to store the value in the DB. Putting it in the DB is just a way for you to allow it to change dynamically. So you could simply change the contents of the file instead of the path to a different file.

I think the trade-off is between how flexible you want the image download to be and how long it takes you to implement. Clearly, hard-coding the name gets you something working faster.

Yeah, I'll have to figure out which is best.

Thanks for your help everyone, really appreciate it

 

[NoNameBrand]

Could I do that? Have a text file called version.txt with 1.00 written in it?
I don't know how I "connect" to the web server? Do I just try and read the file at www.example.com/currentversion.txt??

Depends on what the app is written in, I guess. I haven't done any Windows programming in years. The alternative is opening a DB connection across the internet from your app to your server, right? I think downloading and parsing a document from the web is probably the better option - whether that document is a file or script-generated based on DB content is up to you.

 

[savar]

Hi,

I have a program that checks if its up-to-date when it starts up,
I figure I need an item in my MySQL database called "version" with a value of 1.00.

Now, I've never done anything like this before. I wonder, am I supposed to just go and manually edit and increase that version number when I release an update for the program, or is there some better or proper way to do it?

My second question is, how do I go about storing an image I want the program to download later?
Do I store it in the MySQL database, or on the website? (this isn't an image you'll see on my actual website)

Kind Regards

What?! What kind of application is this? Why would you assume the user has MySQL installed? Are you going to force them to install it?

Or it your MySQL server, and they are accessing it remotely? If so, that sounds like bad idea. Are they accessing it on an unencrypted port? Is your username and password sent in clear text?

You should not be modifying databases by hand in a production environment. Write migration scripts to do the work for you. That way you can test it before you deploy it and have confidence it will work right the first time.

Don't store images in your database. Store them in the filesystem and store the path to the file in your database. (Probably a relative path to the file, not an absolute path, so that you can move the directory that the files are in later without breaking everything.)

 

[MythicFrost]

Depends on what the app is written in, I guess. I haven't done any Windows programming in years. The alternative is opening a DB connection across the internet from your app to your server, right? I think downloading and parsing a document from the web is probably the better option - whether that document is a file or script-generated based on DB content is up to you.

Ok thank you

Don't store images in your database. Store them in the filesystem and store the path to the file in your database. (Probably a relative path to the file, not an absolute path, so that you can move the directory that the files are in later without breaking everything.)

Will do. I guess I can have a table "images" with a VARCHAR in each row with the path pointing to the place on my website to download the image.

Doesn't that mean you can navigate to www.example.com/myimage? I don't want that - only the program should have access to the images.

I guess I mean, can I have an image on the website server that can be accessed by my program, but not navigated to on the website?.

What?! What kind of application is this? Why would you assume the user has MySQL installed? Are you going to force them to install it?

Or it your MySQL server, and they are accessing it remotely? If so, that sounds like bad idea.

It's the MySQL DB attached to my website that I can use. The app will need to check that DB and also add some new rows etc...

Are they accessing it on an unencrypted port? Is your username and password sent in clear text?

Thats true when I'm connecting to the DB via PHP in my web pages that I'm building that connect to the DB.

$con = mysql_connect("localhost", "myusernameforDB", "password") or die("Unable to connect to MySQL");

I will probably connect the exact same way from my program. Is that bad?

You should not be modifying databases by hand in a production environment. Write migration scripts to do the work for you. That way you can test it before you deploy it and have confidence it will work right the first time.

I didn't think so, what is a migration script though? Do I write some script in PHP to change particular things? How exactly does that allow me to test it before actually making the changes?

I appreciate the replies very much!

Kind Regards

 

[NoNameBrand]

A migration script can be written in anything you want; it's typically a collection of SQL queries with data manipulation/generation in the middle. At the most basic, it's just a list of SQL commands in series that MySQL executes in order.

For example:
I might want to add a new table and populate it based on a number of different other tables, and then add a column of foreign keys to the new table to an existing table. A PHP (or whatever) script would create the table, and do selects and manipulate the results and generate & execute insert queries, and update the existing tables as needed.

It's the MySQL DB attached to my website that I can use. The app will need to check that DB and also add some new rows etc...

Write a server-side client to receive info from your app. You could use PHP and have your Windows app generate a HTTP request to submit/retreive data, or you could write it in C and have your app open a TCP socket - whatever you want.

I will probably connect the exact same way from my program. Is that bad?

Yes - probably too easy to spoof the connection and allow SQL injection into your DB. If you write your own server-side DB client (as above), you can sanity-check everything.

 

[jpyc7]

Ok thank you

Will do. I guess I can have a table "images" with a VARCHAR in each row with the path pointing to the place on my website to download the image.

Doesn't that mean you can navigate to www.example.com/myimage? I don't want that - only the program should have access to the images.

I guess I mean, can I have an image on the website server that can be accessed by my program, but not navigated to on the website?.

With respect to preventing downloads, I don't really think you can prevent that, although you can make it harder. One way to prevent other browsers from downloading the image is to add some sort of authentication that only your program knows for that directory. With an Apache server, this is usually done with a .htaccess file. Of course, this isn't hard to get around, since someone could just sniff the connection and see the authentication information used.

Basically, if your program is going to download the image via the HTTP protocol, then any HTTP client program could conceivably also do the download. As NoNameBrand mentioned, you could implement a different protocol on another port. Another option is to use HTTP and just encrypt the image and let your program decrypt it.

As for preventing navigation, just don't have any links to the image directory. I don't think web crawlers can find it then and you can also put in directives that tell web crawlers not to index that directory.

 

[MythicFrost]

All right thanks guys, whats the easiest way for me to download an image there?
Technically I don't have a lot of time, so I'm looking for the quickest way to set it up.

For example, all I need is to store one image somewhere that I can download it to the computer and then use that image. I'm definitely thinking of storing the image paths in the database, and then having the files on the website.

Is there a simple way to say: "Hey I wanna retrieve this image from the website"...?

To be honest I don't care if the image can be downloaded by others, as long as you can't easily navigate to it -- which as said you just don't make files that can navigate to it which won't be a problem.

 

[NoNameBrand]

As for preventing navigation, just don't have any links to the image directory. I don't think web crawlers can find it then and you can also put in directives that tell web crawlers not to index that directory.

You could also have a PHP script serve up the images from a directory outside the document root, and then the client has to have some sort of established authentication with your PHP script.

Is there a simple way to say: "Hey I wanna retrieve this image from the website"...?

Aren't you now asking us how to program a Windows app? You might have reached the limits for this particular forum...

 

[MythicFrost]

Aren't you now asking us how to program a Windows app? You might have reached the limits for this particular forum...

No, I know how to download a file I think, I just need to know how to store it?
Can I just upload the image to my website, and then I'll just download it?

 

[NoNameBrand]

Can I just upload the image to my website, and then I'll just download it?

At the most basic, yes. You could put a whole lot of extra stuff in between though, to restrict or measure access, etc.