Amateur Dev Q: Creating Front End for SQL Server DB

[WWGD]

Hi All,
I am kind of new to this, please be patient. I appreciate your input
I have a SQL Server DB, I am trying to create a remote front end to give graphical, or at least simple access
to the DB (Client-Server, of course).
I would appreciate some suggestions. This is my way of breaking things down:
1) I need to have the front end accept input.
2) Input must be then relayed to back end
3) Input/device must be given access to the backend DB. I guess this is a matter of granting permissions in SQL Server. I guess I need SQL Server authentication here. So I must enable TCP/IP and create a permission through configuration manager

4) I guess I need some code to tell the input string where to go, so it can "travel" to the database, be turned into a SQL query, run, and then have the results returned to the client/front end. I think this is usually done using VB, right?Or do I write a connection string?

Thanks.

 

[DaveC426913]

Your data entry form cannot access the database without a middle layer. So you're going to have two layers:

1. your client-side code - HTML, and code (usually Javascript) will be the the building blocks of your input form and will communicate - via http POST and GET - with ...
2. your server-side code - (maybe PHP or node.js) that will take the data your client-side sends to it, and will interact with the database.

Visual Basic is kind of another animal.

 

[WWGD]

Your data entry form cannot access the database without a middle layer. So you're going to have two layers:

1. your client-side code - HTML, and code (usually Javascript) will be the the building blocks of your input form and will communicate - via http POST and GET - with ...
2. your server-side code - (maybe PHP or node.js) that will take the data your client-side sends to it, and will interact with the database.

Visual Basic is kind of another animal.

Thanks Dave, do you know of any template code so I can adapt it?

 

[DaveC426913]

You'll want to pick a language first. Then go looking for basic projects that others have created, something like PHP basic CRUD*.

*Create/Read/Update/Delete.

You'll need to spend some time just setting up an environment, where you server-code can correctly access the db even before it can transfer data.

Just looking at someone else's code won't get you far. It is well worth your time to spend the $25 or so and take an online course in the technologies you'll need. I use Udemy to learn new technology. Something like 'Basic database access with PHP'.

 

[WWGD]

You'll want to pick a language first. Then go looking for basic projects that others have created, something like PHP basic CRUD*.

*Create/Read/Update/Delete.

You'll need to spend some time just setting up an environment, where you server-code can correctly access the db even before it can transfer data.

Just looking at someone else's code won't get you far. It is well worth your time to spend the $25 or so and take an online course in the technologies you'll need. I use Udemy to learn new technology. Something like 'Basic database access with PHP'.

I understand and agree. I would like to do both; just something psychological about being able to see the output.

 

[pbuk]

What is your end goal?

• Viewing your SQL Server data locally?
• Viewing your SQL Server data on the web?
• Learning how to create a desktop application to access SQL Server data?
• Learning how to create a web-based application (like XenForo, the software PhysicsForums uses although I don't believe it uses SQL Server to store its data)?
• Something else?

 

[newjerseyrunner]

This is quite easy, you just have to know all of the components.

First, you need your front-end actually coded. Buttons, inputs... these are handled in HTML and Javascript. When you press a button, you will either send a GET/POST request to your backend and update your page, or you will redirect to a whole new page. Either way, your link will look something like this myserver/request.php?info1=hello&info2=world Everything after the question mark, is a parameter.

In the backend, you need a server. This is a program called Apache. It's configured to listen for web requests and respond to them. If PHP is requested, it will also run the php.

The PHP is what will actually talk to your database. All of those parameters from the front-end are available to you in $_GET['info1'] and $_GET['info2']. You will use functions like mysql_connect and mysql_query to then send what you need (or fetch it) from the database. You then print out what you want apache to send back to the client.

You absolutely must learn a concept called input sanitization. If you have a SQL query "SELECT * FROM users WHERE userid = $id" and I provide you the username "1; DELETE users" I can totally fubar your database.

 

[pbuk]

This is quite easy, you just have to know all of the components.

First, you need your front-end actually coded. Buttons, inputs... these are handled in HTML and Javascript. When you press a button, you will either send a GET/POST request to your backend and update your page, or you will redirect to a whole new page. Either way, your link will look something like this myserver/request.php?info1=hello&info2=world Everything after the question mark, is a parameter.

In the backend, you need a server. This is a program called Apache. It's configured to listen for web requests and respond to them. If PHP is requested, it will also run the php.

The PHP is what will actually talk to your database. All of those parameters from the front-end are available to you in $_GET['info1'] and $_GET['info2']. You will use functions like mysql_connect and mysql_query to then send what you need (or fetch it) from the database. You then print out what you want apache to send back to the client.

You absolutely must learn a concept called input sanitization. If you have a SQL query "SELECT * FROM users WHERE userid = $id" and I provide you the username "1; DELETE users" I can totally fubar your database.

Even assuming that WWGD’s answer to my previous question is “I want to learn how to create a web based application like XenForo”, nobody creates applications coded the way you suggest any more.

 

[WWGD]

Even assuming that WWGD’s answer to my previous question is “I want to learn how to create a web based application like XenForo”, nobody creates applications coded the way you suggest any more.

Thanks , both: Mr Anchovy, I know, I am teaching myself and just picked up a few things here-and-there. I will give you a more thorough answer ASAP. Very helpful, both of you.

 

[pbuk]

If you are committed to "the Microsoft way" then this is probably a useful link: https://sqlchoice.azurewebsites.net/en-us/sql-server/developer-get-started/ - pick a language and OS you are already familiar with and dive in.

But I'm going to give you a different suggestion: abandon SQL Server - there are some benefits to using the Microsoft stack in a corporate environment, but I can't see any for a lone coder. What attracts you to SQL Server?

 

[WWGD]

If you are committed to "the Microsoft way" then this is probably a useful link: https://sqlchoice.azurewebsites.net/en-us/sql-server/developer-get-started/ - pick a language and OS you are already familiar with and dive in.

But I'm going to give you a different suggestion: abandon SQL Server - there are some benefits to using the Microsoft stack in a corporate environment, but I can't see any for a lone coder. What attracts you to SQL Server?

It is the only SQL Database I am familiar with and I don't have much time to learn something new. I am trying to create a database for someone and then give them remote access to it through some GUI or GUI-like (e.g, QBE-- I am thinking Access if possible). Given I am a beginner, I am trying to stick to what I know and try something new once I am at a more advanced level.

 

[pbuk]

It is the only SQL Database I am familiar with and I don't have much time to learn something new. I am trying to create a database for someone and then give them remote access to it through some GUI or GUI-like (e.g, QBE-- I am thinking Access if possible). Given I am a beginner, I am trying to stick to what I know and try something new once I am at a more advanced level.

In order to create a web application implementing this you would have to spend many hours learning new stuff (probably many hundreds of hours), whereas it would take almost no time to learn the differences between an open source database (PostgreSQL, Maria DB or MySql) and MS SQL. The advantage of open source is that it is much cheaper and easier to deploy than a Microsoft stack.

I think you are seriously underestimating what is required to create and maintain an application for someone else to use. If this was just for fun I'd be happy to give you more suggestions, but as it is I'm afraid my only suggestion now is not to embark on this project. If you want to give someone some data to use, send them a spreadsheet.

 

[WWGD]

In order to create a web application implementing this you would have to spend many hours learning new stuff (probably many hundreds of hours), whereas it would take almost no time to learn the differences between an open source database (PostgreSQL, Maria DB or MySql) and MS SQL. The advantage of open source is that it is much cheaper and easier to deploy than a Microsoft stack.

I think you are seriously underestimating what is required to create and maintain an application for someone else to use. If this was just for fun I'd be happy to give you more suggestions, but as it is I'm afraid my only suggestion now is not to embark on this project. If you want to give someone some data to use, send them a spreadsheet.

No, I don't just want to give someone some data. I want to create a database for them and give them access to it and the ability to do basic SELECT, UPDATE queries. I am willing to consider prefabs in other SQL if this simplifies things. EDIT : OR do the closest thing to this that I can do.

 

[pbuk]

If I understand you correctly, that is really easy to achieve, and here's how to do it. It will cost you USD5 a month to run, assuming you don't have a huge amount of data and there is only the two of you using it. You are going to need a lot of help along the way, but the best place to get that is by googling the particular problem you have and/or specific tutorials.

1. Create an account at Digital Ocean (or another cloud provider, you could use Microsoft Azure I suppose).
2. Create a new Ubuntu server following these instructions.
3. Add a LAMP stack to your server following these instructions. This is Linux (the operating system - much more suitable for this purpose than Windows), Apache (that delivers the web pages), MySql (an open source database that is very similar to SQL Server), PHP (a programming language).
4. Follow these instructions to install PHPMyAdmin.
   
5. Find a tutorial on using PHPMyAdmin and create your database tables. Export data from your MS SQL database into CSV files and import them using PHPMyAdmin.
6. Create another PHPMyAdmin user for your friend with as much or as little privilige as you like on the appropriate database and give them the password and IP address.

Good luck!

 

[WWGD]

This is quite easy, you just have to know all of the components.

First, you need your front-end actually coded. Buttons, inputs... these are handled in HTML and Javascript. When you press a button, you will either send a GET/POST request to your backend and update your page, or you will redirect to a whole new page. Either way, your link will look something like this myserver/request.php?info1=hello&info2=world Everything after the question mark, is a parameter.

In the backend, you need a server. This is a program called Apache. It's configured to listen for web requests and respond to them. If PHP is requested, it will also run the php.

The PHP is what will actually talk to your database. All of those parameters from the front-end are available to you in $_GET['info1'] and $_GET['info2']. You will use functions like mysql_connect and mysql_query to then send what you need (or fetch it) from the database. You then print out what you want apache to send back to the client.

You absolutely must learn a concept called input sanitization. If you have a SQL query "SELECT * FROM users WHERE userid = $id" and I provide you the username "1; DELETE users" I can totally fubar your database.

To clarify, what I am aiming for is to give access to a few people in a company, to company data , together with the option of doing basic SELECT, UPDATE, queries. This is not intended as , e.g., a general website to sell something or provide info. I mean, I am interested in the issue from this perspective too, but I am trying foremost to do a database for a small company, giving access just to certain employees. I know how to do some basic admin/security for local databases through Logins, Users and Roles, but I (clearly) know little about granting different types of access remotely.

Mr Anchovy: I am following up on your ideas, I am trying to understand this better for the sake of my interest in learning the general area. Is it possible to restrict access to this webpage? Maybe have it posted within a local (say Apache) server or within/inside a firewall? If not, how are these things done?

 

[WWGD]

Basically, I am trying to create a database and give access to it to a small group within a company. Is there a way of doing this within the setup described, Anchovy/Runner?
Thanks again to both.

 

[DaveC426913]

One problem you may encounter is that, if not done with expertise, you can very likely be exposing that company's data to uninvited guests, who will easily be able to alter - and delete - any data they want. This will open you up to liability.

I built a site for myself. I had 10+ years of experience in front end web dev, and enough back-end experience to get by. After using it for a few months, I discovered that my data had easily been accessed, and they had added zillions of spam links to my site. They were invisible, so I didn't even see them until I looked at the rendered code, and then traced them back to the database itself, where they had inserted them.

I had password security on my site, but they had gotten around it - most likely with simple automated tools (it wouldn't surprise me to learn that no human had ever needed to look at my site or code).

These days, every site on the internet is pinged (pung?) dozens to hundreds of times day by automated systems, looking for security holes.


I've been a developer for over 25 years now (albeit front end), and I still wouldn't touch your project - not without hiring a security guy to oversee it.

I don't want to discourage you, but a little knowledge can be a catastrophic thing when it comes to a company's dependency on the security of their data.

 

[DaveC426913]

In other words, this:

 

[pbuk]

To clarify, what I am aiming for is to give access to a few people in a company, to company data , together with the option of doing basic SELECT, UPDATE, queries. This is not intended as , e.g., a general website to sell something or provide info. I mean, I am interested in the issue from this perspective too, but I am trying foremost to do a database for a small company, giving access just to certain employees. I know how to do some basic admin/security for local databases through Logins, Users and Roles, but I (clearly) know little about granting different types of access remotely.

Mr Anchovy: I am following up on your ideas, I am trying to understand this better for the sake of my interest in learning the general area. Is it possible to restrict access to this webpage? Maybe have it posted within a local (say Apache) server or within/inside a firewall? If not, how are these things done?

I had made an assumption that this was a personal hobby project, which I now see is incorrect. Please ignore all my previous suggestions and take Dave's advice.