Connecting to an online SQL database [n00b]

Question

Hi I have little knowledge of using web technologies particularly using online databases. I can do basic php asp SQL and I know how to construct a proper database. Anyway what I want to do is this: I have a host capable of providing me with MySQL. Now how do I communicate with this database? I'm writing a C# application which I want to be able to communicate with this database. For example say there was a table called stock. I want to be able to pass a stock name to the MySQL technology and receive the stock quantity. How is this done? Do you have to get data through a middle man like a php/asp script or does the MySQL technology create a session between the webserver and the local machine (client). Note at the moment my host is a free 000webhost.com. Thanks Thomas

theLizard · Answer

I completely understand what you are saying... Q's. For you Sam How many desk top applications have you written that connects to a web server in real time. How many web applications have you written that connects to databases. How many dedicated or virtual dedicated servers have you worked with. How many user controls components that install in IDE's that dynamically connect to Sql Serve MySql Access Oracle have you written. What types of measures have you employed to ensure security of database systems. Reading out of a book is one thing having experience is another and sometime what the book tells you is not always how it is done. Have you heard this before There are hundreds/thousands of books written about fishing they tell you what the best bait is where to catch them and so on the only trouble with these books is that I am not aware of ANY fish in the ocean rivers or lakes that has read one are you. What was that other thing you misunderstood oh yeah it was GetWindowText... which in the end you you acknowledged everything I said (not directly) in the last post of the question. I am not here to criticize you or anyone else but you seem to want to answer questions just for the sake of answering questions regardless of if you know much about the subject or not if you have not noticed yet I do NOT answer questions in matters I have had NO experience with.

Sam Hobbs · Answer

theLizard you are not understanding what I am saying but instead of trying to understand you say I am not understanding. So it is futile to continue.

theLizard · Answer

Impossible! Read what I said again; the script is executing in the server; the userid and password are not sent out. You are NOT understanding what is being said. There are two sets of userid passwords one is the connection string userid and password ( without access to webconfig yes you cannot gain access to the credentials) the other is the login user user id and password the connection string password is never given in the desktop application scenario using a surrogate it is only the user wanting to log in whose password and user id is exposed. That is why I said  Having said this it is not good practice to do that but not out of the question it is best to have server side scripting to Marshall the requested data as needed this is for security reasons.  From a desktop application you CANNOT send an encrypted connection string to the server at any time but you CAN send encrypted user login credentials. Do you understand what I am saying..

Sam Hobbs · Answer

I said:  You are not likely to expose the userid or password to the world if they are in the script and used in the script  .... theLizard said:  if you are logging in from the descktop application if it is not encrypted then it is sent in plain text in which case it is exposed . Impossible! Read what I said again; the script is executing in the server; the userid and password are not sent out.

Sam Hobbs · Answer

Yes I might be wrong about all the records (such as 5000) being sent to the client system. I am familiar with many database systems and I probably have the details confused. So let's assume that the SQL gets executed in the server. Even then all data that results from the SQL gets sent to the client. If data is put into a table (a HTML table) then all that data goes to the client even if the data is scrolled. What is normally done is when data is scrollable it is not all sent to the client at once; the client can send a request for another page of data. Such as when you do a search using a search engine; not all data is sent at one time only pages at a time. We have to wait for the request to go to the server and results to come back. There are very few sites that send all the data at once; most send just a page at a time.

theLizard · Answer

PHP would execute in the server and none of the script is sent to the client. You are not likely to expose the userid or password to the world if they are in the script and used in the script or stored in a file that is at least as secure as the script is. If you do all that then the only data sent out that would be vulnerable to being used by unintended people would be the data in the HTML page. If that data also needed to be protected then you probably could use a secure connection (https) but I doubt you can do that with a free account. I think (I am not sure) that a https connection requires a certificate or something like that. Sorry Sam but if you are logging in from the descktop application if it is not encrypted then it is sent in plain text in which case it is exposed...

theLizard · Answer

Ofcourse how could I be so stupid! If I have a direct connection then all SQL responses would be handled locally which is a sercuity risk (and sercuity is an issue). No the SQL responses would be handled by the server sql requests would be made locally. Looks like php is the option for me. Apart from perhaps speed is there anything wrong with mysite.com/login.php?username=tom&password=hello then getting a the php script to echo a text page which is read from my program. From an experienced programmers point of view would this be considered a good solution? Yes this is an option but remember the amount of data you will need to forward to the local machine means that how you write the application will be important. To clarify things do not think in terms of text page in the same context as you would see it in a browser all the php needs to do is stream the data in a formatted way that your application can then process for example SELECT * FROM transactions WHERE transactionDate = '2010/08/01' the stream would then look something like 2010/08/01Widget15$23.45$351.75; ...; ... Ofcourse you see the password was sent unencrypted. How dangerous is that. How easily can it be intercepted? Very I suppose I can minimize the sercuity risk by: Having login attempt per unit time quotas Encryption But encryption would be heavy I suppose. Talking of encryption should I use assymetric? If I used symmetric I would have the key hard coded into my program. How easy is it for someone to dig it out? The only way around this is to encrypt the data at the local machine so that the login is encrypted before you send it to the server in this case the values are stored in the database as encrypted values so you would be querying the database for the encrypted value not the plain text version. If your desctop application is written in C# you could yous the rijendale (not sure about the spelling) method where you supply the seed values these do not need to be stored on the local machine by memory there a 5 seed values for rijendale you could have 2 hard coded and 3 stored in the database in plain text you would then pol the database when connecting for the 3 seed values and combine with those that are hard coded this would make it almost impossible for any one who does not have access to the application to be able to use the plain text seed values.

Sam Hobbs · Answer

PHP would execute in the server and none of the script is sent to the client. You are not likely to expose the userid or password to the world if they are in the script and used in the script or stored in a file that is at least as secure as the script is. If you do all that then the only data sent out that would be vulnerable to being used by unintended people would be the data in the HTML page. If that data also needed to be protected then you probably could use a secure connection (https) but I doubt you can do that with a free account. I think (I am not sure) that a https connection requires a certificate or something like that.

theLizard · Answer

All SQL databases were originally designed for database servers of some kind not specifically the same computer and they ALL need drivers to connect to no matter what. Quote Sam. Note that sending a web page with the data formatted in the page is normally more efficient than sending all the data all at once. Let's say that there are 5000 records each record being 1000 characters. Do you really think that sending 5 MB is efficient? End Quote Sorry Sam but having a formatted HTML page is as usefull as hens having teeth in the type of application Thomas wants he simply wants the data nothing more nothing less. I am sure that if he wanted a web based application he would not be talking about a pc based executable in which case a HTML generated by server side script would be appropriate. Even a web based solution would have issues with 5000 records since 5MB as you put it would still be sent to the web browser no matter what 5Mb is still 5Mb no matter how it is sent. Good database design is one where you manage volume by sending queries to minimise the amount of data being sent ie if your query is simply SELECT * FROM transactions then you will get ALL the transactions in the database and that could well be hundreds of thousands of records but if your query is SELECT * FROM transactions WHERE transaction date > '2009/03/08' then you will ony get those transactions or if your query is SELECT TOP 100 FROM transactions then you will only get the first 100 records so how you create your queries determines how much data is sent. Try loading 5000 records on a web page and see how it slows down it will be no difference to sending the data direct to the local machine using a direct connection. Sorry again Sam but I am not disagreeing simply stating the facts from my own experience with these types of issue. As for allowing remote connections to databases by hosting providers you may be right with free hosting sites but I doubt that in any case since for anyone having an account with such providers credentials are provided for you to remotely manage the database on the server therefore the only way that a host could stop remote connections is to place a limit on how many connections the database can handle at any given time. Doing this would make web based database systems useless since everyone browsing a web site having a forum or data driven web site makes a remote connection to the database using the credentials given by the host in these cases the credentials are stored in the web.config and the same credentials are used by everyone browsing these types of web sites take face book for instance hundreds of thousands of users connect to the database when they log on each day. All databases are vulnerable on the internet no matter what even if you have taken the most severe measures to ensure that they are not someone will always be smarter than the guy who developed the security measures to stop you from gaining unauthorized access. Sorry again Sam but the type of Remote Connections you refer to is a completely separate issue from this one as the type of Remote Connections hosts do not allow are the ones that give access to the host system where by you can change system settings and other operating system tasks. There are NO hosting providers paid or otherwise that will allow Remote Connections to their system unless you have a Virtual Dedicated Server or a Dedicated Server in which case you can have as many Remote connections as you allow. Connect to MySql - Free Web Hosting Forum ; This needs to be read in context I think you are misunderstanding what Remote Access means. You need to find out what upgrading your account means. FYI. I have had Virtual Dedicated Servers and it was up to me how many Remote Connections I allowed not the host I also have developed many web applications and have my own data driven web site I also have a pc based application (you can download a trial of this from my web site DeskTopOrganizer) that connects to a MySql database and periodically pols the database to ensure that the installation of the application is registered correctly or if it is a trial version which the cuts out after the trial period is over. With respect Sam if i disagree with what you have said it is for a reason NOT because I disagree for the hell of it.

Thomas Morrison · Answer

Ofcourse how could I be so stupid! If I have a direct connection then all SQL responses would be handled locally which is a sercuity risk (and sercuity is an issue). Looks like php is the option for me. Apart from perhaps speed is there anything wrong with mysite.com/login.php?username=tom&password=hello then getting a the php script to echo a text page which is read from my program. From an experienced programmers point of view would this be considered a good solution? Ofcourse you see the password was sent unencrypted. How dangerous is that. How easily can it be intercepted? I suppose I can minimize the sercuity risk by: Having login attempt per unit time quotas Encryption But encryption would be heavy I suppose. Talking of encryption should I use assymetric? If I used symmetric I would have the key hard coded into my program. How easy is it for someone to dig it out? Thanks Thomas

Sam Hobbs · Answer

The first part of what you say is good. Yes MySQL was originallly designed for use in the same computer. Yes use a server side language such as PHP or C# ; note that C# is also server-side. Normally the server-side script generates HTML that gets sent to the client. The HTML can have JavaScript but not C#. Note that sending a web page with the data formatted in the page is normally more efficient than sending all the data all at once. Let's say that there are 5000 records each record being 1000 characters. Do you really think that sending 5 MB is efficient? I was a bit strong in my first reply because it helps for you to understand that there were some important misunderstandings. Unfortunately theLizard enjoys disagreeing. Note that when theLizard is mistaken theLizard won't admit it. theLizard is correct about the security risks of your data. There is another critical security risk though; allowing remote connections can make a database vulnerable so many hosts don't allow remote connections. Remote connections can require extra work to keep the entire host safe. See Connect to MySql - Free Web Hosting Forum ; if you want to use a 000webhost free web site you can't use a remote connection and there is no way around that unless you pay for the site.

theLizard · Answer

No you can have the local machine do all the work without server side script the reason that MySql needs to be installed on the local machine is so that you have the drivers needed to connect to the database on the host. I have done exactly that the only thing you need for a local machine to connect to the database is the IP address User Name and password all queries can be sent from the local machine ie. SELECT * FROM Clients WHERE clientName = 'any client' will return the result set directly to that local machine. You then handle the return data as if it was on the local machine that is fill the text boxes and other controls as you would in any locally installed installation of MySql. The main reason why this is not a preferred method is that all data sent can be intercepted by anyone who has the knowledge or inclination to do that if you want to proceed down this path you may want to consider encrypting the data at the local machine and parse the encrypted values to look for or insert into. Obviously this would have it's own draw backs since you can only send encrypted values ie. SELECT * FROM Clients WHERE clientName = '^(*(*&^&^(&%$#$_)@(*' but there is nothing to say that you cannot do it the more simpler way no encryption at all.. If your data is not sensitive then by all means a local app will do fine but remember you do need to supply the credentials supplied to you by the host provider on each installation of your application and that MUST be stored somewhere in an encrypted manner so that your web based database system is not compromised. Yes you are correct that a server side script will place a heavy burden on work load and possibly slow things down more than a direct connection would if your connection speeds are high. What you need to do is decide on (a) Is the application one which requires heavy security (b) Do you mind having your credentials embedded in your application. (c) What is the easiest for you to do. VERY IMPORTANT: If you want to use the simpler way you MUST be vigilant in your opening and closing database connections on each request ie. open connection do the work close the connection you cannot afford to leave a connection open as it would quickly exhauset the connection pool on the host machine and this would lock out any connection attempt until a connection hadle is available. If you want to test connecting directly to your host you can do that by creating an ODBC DSN giving the IP address user name and password for it's connection values then use MySql Query Browser to connect to the database on the host server it will work..

Thomas Morrison · Answer

Okay. So are you saying that MySQL isn't really designed for client applications to communicate with ie MySQL likes to be on the same computer/server as the script/program accessing it is on? Is my best bet now to use a server side language (php) and get http responses http://mywebsite.com/login.php?username=x1&password=x2 the php file does the work (creating tables querying modifying) and the spits out the respone into a webpage. the C# application reads this page and parses out information Is a setup like that good? What about under heavy traffic? It's all string based. Does that mean slow? I would of thought something like creating a session between a client machine and the server and exchanging data (which I thought the MySQL connector could do) would be the most elegant solution. What do people suggest/think? Thanks Alot Thomas

theLizard · Answer

Quote Many users have the program. they will all be logining in and out throught out the day on their computers at home. So I guess what  I assume your program will execute in the host where the MySQL is. is incorrect as there will be no executable online users will have an executable on there pc at home. End Quote. Hi Thomas You are correct you do not need server side script on the host for MySql or MS SQL. Applications on the local machine can access web based sql servers by providing a IP address and the user name and password you are given by the host provider. The other thing that you need to take into account is that each machine were the app is installed will also need to have MySql installed wit the MySqlConnect drivers for .Net or ODBC. Having said this it is not good practice to do that but not out of the question it is best to have server side scripting to Marshall the requested data as needed this is for security reasons. Bottom line is that you do need a middle man as you mentioned I have done similar with a C++ application on the local machine and aspx (C#) on the host doing all the database connectivity in this scenario you send the log on user name and password send you request for information and the server side script does the actual serving of the data but that is an enormous amount of work.

Sam Hobbs · Answer

If the scripts are written in PHP or for the ASP environment then  there will be no executable online  is incorrect. PHP and ASP are server-side; they do not execute in the client. Java normally executes in the client. Note that this forum is for C#; are you sure that 000webhost.com has .Net? Probably not. As I said there are abundant articles and samples of using MySQL in PHP.

Thomas Morrison · Answer

Here is the situation. There is a website which runs MySQL which has a forum etc. Having a forum requires a MySQL database which has lots of tables e.g Username password I am developing a program. Part of this program has a login area where the user will need to enter there username and password (same as on the forum) to access it. Many users have the program. they will all be logining in and out throught out the day on their computers at home. So I guess what  I assume your program will execute in the host where the MySQL is. is incorrect as there will be no executable online users will have an executable on there pc at home. So my question(s) now is: How do the users best connect? Do I have a php script that lives on my server and users pass there username and password to it and that spits out a html response. OR is there technology so I can make a direct connection between the database and my software and exchange data. Thanks Thomas

Sam Hobbs · Answer

I assume your program will execute in the host where the MySQL is. MySQL has an ADO .Net driver that is probably installed. Use it to use MySQL in the manner that ADO .Net is used for other databases. You might need to add it to the Visual Studio toolbox. There are abundant articles about using native MySQL. I think articles about MySQL in another language such as PHP would be relevant except for minor syntax differences.