| Groups | Blog | Home |
sql server (microsoft) : SQL beginner help
I have SQL Express installed and I can access it using C# and SQL connection
manager but I'm confused on how to use this for my application. What I want to do is host a database on a web server(which uses mysql) that stores information for users. I have no idea how SQL works except for a very basic understanding of databases. What I was going to do was write a C# application that accessed the database and let the usuer query the database that way. But now I'm concerned about security and stuff. A friend of mine told me that essentially I have to write an intermediate server that sits between the SQL database server and the client so that the client cannot ever get any direct access to the database. Is this true? If so then how do I create such a program as it would require the isp to run the program(which is highly doubful that they will?). It also seems like twice the work as essentially it would just be a front end on the SQL database. Basically the database will just store information(obviously) and the user will use some interface to access the information(which is about books). I was thinking about using a web interface instead of an application since it would be easier but I do need to access the clients computer and I don't want to use java to do this. Basically the database is for books sorta like CDDB but different than the book information databases around now. What I'm asking is how does one programmatically interface with an SQL database? Obviously not through scripts and its usually done transparently but I don't know if they are dynamically creating the scripts and sending it directly to the server or if there is some other means? Like when I access a webpage that displays information that obviously comes from a database, is there a php or javascript that gets the information from the sql database and displays it or does it go through some intermediate server for security reasons? The whole reason is simply that I don't want to have some users having full access to the database and ruining it by adding wrong information or deleting it. I want to keep track of which user did what so they can modify what they have added but not change what someone else has done. I have no idea if this is handled by SQL directly or if I have to write a front end to do it? (and the front end is the only thing the clients see) Thanks, Jon
[quoted text, click to view]
On Feb 8, 6:32 pm, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote: > I have SQL Express installed and I can access it using C# and SQL connection > manager but I'm confused on how to use this for my application. > > What I want to do is host a database on a web server(which uses mysql) that > stores information for users. > > I have no idea how SQL works except for a very basic understanding of > databases. What I was going to do was write a C# application that accessed > the database and let the usuer query the database that way. But now I'm > concerned about security and stuff. > > A friend of mine told me that essentially I have to write an intermediate > server that sits between the SQL database server and the client so that the > client cannot ever get any direct access to the database. Is this true? If > so then how do I create such a program as it would require the isp to run > the program(which is highly doubful that they will?). It also seems like > twice the work as essentially it would just be a front end on the SQL > database. > > Basically the database will just store information(obviously) and the user > will use some interface to access the information(which is about books). I > was thinking about using a web interface instead of an application since it > would be easier but I do need to access the clients computer and I don't > want to use java to do this. > > Basically the database is for books sorta like CDDB but different than the > book information databases around now. > > What I'm asking is how does one programmatically interface with an SQL > database? Obviously not through scripts and its usually done transparently > but I don't know if they are dynamically creating the scripts and sending it > directly to the server or if there is some other means? > > Like when I access a webpage that displays information that obviously comes > from a database, is there a php or javascript that gets the information from > the sql database and displays it or does it go through some intermediate > server for security reasons? > > The whole reason is simply that I don't want to have some users having full > access to the database and ruining it by adding wrong information or > deleting it. I want to keep track of which user did what so they can modify > what they have added but not change what someone else has done. I have no > idea if this is handled by SQL directly or if I have to write a front end to > do it? (and the front end is the only thing the clients see) > > Thanks, > Jon OK Jon, that was a lot of info, so I'll try to cover it all. I'm coming from a very similar background, C# developer who had to learn SQL databases to do some projects. SQL Express will be good practice for you, but your final product will not use it if the server you're connecting to has MySQL. I haven't used MySQL before, but it shouldn't be a huge change to your code. You will just use a different DataAdapter. Now, as for how to create your product. It seems like a web interface will be the easiest thing for you. It might not be as familiar as a C# application, but it's better suited to your goals. The biggest thing I got from your post is you need to balance the effort you're putting into this and the functionality you need. You mention very specific security needs. To have a user have control over things they add, but not have control over items other people add is possible, but it is significantly more security work. In SQL Server you have security "groups" and users can join one or more groups. So a certain group may have read + write access over one table while another group only has read access. I do not know if this is how it works in MySQL or not. To accomplish your goal of users having access to only certain rows in a table is going to require some server-side code outside of the group security to determine if the user should be able to access a certain row. If you decide to do a C# application instead of a web interface, you do not HAVE to create code to run on the server, but in the end it will be much more useful. If you don't, then each user who is running your app will have to have security access to the instance of SQL Server (again, could be different in MySQL). So you would either have to have an account in the database for each user, or set up general user accounts that the many users share (this can be done in code so they wouldn't know their account details). Then in code you would make a connection over the net to your database. If instead you're just connecting to another app, and that app is handling all the connections to the database then the security model is much simpler. ok, I hope that helps. If it doesn't please post some response questions. Sorry that I don't know anymore about MySQL, there might be some better groups to post MySQL specific questions. Good luck!
[quoted text, click to view]
Jon Slaughter wrote: >> In SQL Server you have security "groups" and users can join one or >> more groups. So a certain group may have read + write access over one >> table while another group only has read access. I do not know if this >> is how it works in MySQL or not. To accomplish your goal of users >> having access to only certain rows in a table is going to require some >> server-side code outside of the group security to determine if the >> user should be able to access a certain row. >> > > Ok, but what is this code? Is it html, javascript, or what? is it SQL > statements that are embedded in the code(Sorta like how I can use SQL in C# > but its just more like a simple wrapper that forwards the SQL statements to > the server)? ASP, in your case. This may be as simple as including UserID as a column in the table, then forwarding SQL statements like: select (list of fields) from the_table where UserID = 'jblow123' (and possibly other conditions) The overall architecture looks like this: SQL <-------------------- ASP <-------------------- end user logged into SQL logged into web site as "website" as "jblow123" The "website" SQL login can read/write any row in the table. The ASP code chooses to read/write only certain rows in response to a
<snip>
[quoted text, click to view] > OK Jon, that was a lot of info, so I'll try to cover it all. I'm > coming from a very similar background, C# developer who had to learn > SQL databases to do some projects. > Thanks, I appreciate your time. [quoted text, click to view] > SQL Express will be good practice for you, but your final product will > not use it if the server you're connecting to has MySQL. I haven't > used MySQL before, but it shouldn't be a huge change to your code. You > will just use a different DataAdapter. > Well, I might end up using MS SQL but might not. A book I was reading says there are some significant differences between the two(well, atleast between MS SQL and Oracle) but my web host uses MySQL and for now I will probably use it. I doubt I will end up using advanced functionality as I just need to store some simple information and have decent security. [quoted text, click to view] > Now, as for how to create your product. It seems like a web interface > will be the easiest thing for you. It might not be as familiar as a C# > application, but it's better suited to your goals. The biggest thing I > got from your post is you need to balance the effort you're putting > into this and the functionality you need. You mention very specific > security needs. To have a user have control over things they add, but > not have control over items other people add is possible, but it is > significantly more security work. What I've been looking at is asp.net and playing around Web Developer but I had some problems with the server so uninstalled and going to reinstall soon(when I tried to add an SQL table it would complain that I didn't have SQL Express installed). [quoted text, click to view] > > In SQL Server you have security "groups" and users can join one or > more groups. So a certain group may have read + write access over one > table while another group only has read access. I do not know if this > is how it works in MySQL or not. To accomplish your goal of users > having access to only certain rows in a table is going to require some > server-side code outside of the group security to determine if the > user should be able to access a certain row. > Ok, but what is this code? Is it html, javascript, or what? is it SQL statements that are embedded in the code(Sorta like how I can use SQL in C# but its just more like a simple wrapper that forwards the SQL statements to the server)? [quoted text, click to view] > If you decide to do a C# application instead of a web interface, you > do not HAVE to create code to run on the server, but in the end it > will be much more useful. If you don't, then each user who is running > your app will have to have security access to the instance of SQL > Server (again, could be different in MySQL). So you would either have > to have an account in the database for each user, or set up general > user accounts that the many users share (this can be done in code so > they wouldn't know their account details). Then in code you would make > a connection over the net to your database. If instead you're just > connecting to another app, and that app is handling all the > connections to the database then the security model is much simpler. > Ok. I was thinking about the having to add each user to the SQL database but that seems like a bad idea? I guess now I'm confused on how the web interface does this better than what can be done in C#? Is it just the tools for web design are geared towards this and so its much easier to do it or is there somethign else involved? [quoted text, click to view] > ok, I hope that helps. If it doesn't please post some response > questions. Sorry that I don't know anymore about MySQL, there might be > some better groups to post MySQL specific questions. Good luck! > Yes, it helps some. I'm still a bit confused but its my fault. I wouldn't worry about MySQL as I'm using MS SQL for now and it won't be for a while that I do anything with MySQL. Let me state what I want again so I'm more clear. I want some interface(web or application) that lets users access a database of information. Essentially the only thing I will be doing on the clients computer is getting files and computing some hashes on them. Now they will add or delete information to the database and each person will have there own little "nook" on the database. For the most part all the information they store can be seen by everyone else(except specific account details) but others cannot delete there own information. So basically I will have a table for each user that contains tables for there account information and there "orders"(well, information they have added). But all the orders for all the users will be viewable but editable by the creater. (Sorta like how you can edit your own messages in a forum but not others). I suppose essentially it is analogous to a forum that I'm wanting to create except the information is different. Hopefully something like this isn't hard to do and ultimately if I could trust the users then I would just have an "open" database where people would just use some nice GUI to wrap SQL statements(instead of requiring everyone to know SQL) to get at the information in the DB. My first thought was to use C# to write the application so it would be client based and I could easily get at the file system. I suppose this is not to hard with Java or other "web" based interfaces(since I do see it happen) but I don't have much experience with them and I my code needs to be client side(don't want to have to upload a file just to compute a hash). I guess I'm just confused on how to go about it. Right now I'm learning SQL but I don't understand how it is used in the "real world". I can create a database and manipulate data using SQL statements and I can easily understand how to create a UI and just wrap these statements for users but I do not understand how the security aspect comes into play and if its something that is done by the SQL Server and I just have to set it up(I know it has security but I'm not sure if its what I'm suppose to use) or do I basically create the stuff myself(Can still use the database to store user information but the "checks" are done outside the SQL Server). for example, I can imagine how the edit message in a forum might be like. One dynamically generates the web page, the user clicks on edit, this goes to some code that checks and see's if the message belongs to the user. It does this by looking in the database for a relationship between the user and the message. If there is a relationship then the user has the right to edit and then it moves onto the edit portion. (the user somehow has to be logged in which I guess one uses cookies to keep track of that?) Anyways, thats my take on how that would work but I could be totally off? Hopefully this makes more sense. I appreciate your time. Thanks, Jon
[quoted text, click to view] "Ed Murphy" <emurphy42@socal.rr.com> wrote in message news:45ccdb5f$0$24503$4c368faf@roadrunner.com... > Jon Slaughter wrote: > >>> In SQL Server you have security "groups" and users can join one or >>> more groups. So a certain group may have read + write access over one >>> table while another group only has read access. I do not know if this >>> is how it works in MySQL or not. To accomplish your goal of users >>> having access to only certain rows in a table is going to require some >>> server-side code outside of the group security to determine if the >>> user should be able to access a certain row. >>> >> >> Ok, but what is this code? Is it html, javascript, or what? is it SQL >> statements that are embedded in the code(Sorta like how I can use SQL in >> C# but its just more like a simple wrapper that forwards the SQL >> statements to the server)? > > ASP, in your case. This may be as simple as including UserID as a > column in the table, then forwarding SQL statements like: > > select (list of fields) > from the_table > where UserID = 'jblow123' (and possibly other conditions) > > The overall architecture looks like this: > > SQL <-------------------- ASP <-------------------- end user > logged into SQL logged into web site > as "website" as "jblow123" > > The "website" SQL login can read/write any row in the table. The > ASP code chooses to read/write only certain rows in response to a > request from the "jblow123" web site login. Ok, so its up to the asp front end to manage security(I guess its better to say permission) rights? When the user logs onto the web site and interfaces with the Asp code it will decide what how to handle what the user is able to do? So ASP has its own log in onto the SQL Database(its own pipe so to speak) but ASP will deal with restricting the users access? So maybe jblow123 can change his own information but cannot change others because the ASP front end is designed to only bring up his own information. e.g., I'm thinking of an example where jblow wants to view his own account information. You design some ASP code that will bring up only jblow's information when he requests it(he can't request others information(or atleast private information) so only way he could mess with others stuff is if the ASP code was buggy/insecure? Basically your telling me that jblow doesn't access the SQL server/database directly like I can when I go write an SQL statement and run it in visual studio? So I get to choose what he can do and what he can't? Since he cannot really get at the ASP code(?) he can't change things and force it to see something he's not suppose to? (like trick ASP into thinking he's jane431 to get here private info?) If this is the case then I suppose its not that difficult. I just have to learn ASP and SQL now ;) Basically the information and the security are handled seperately. I think maybe I now have a mental map of what is going on. Its still kinda fuzzy but after I start working with asp a little in Web Developer I'll probably get a better feel. I really just have no clue what asp is and how it works yet and thats probably why I don't understand how it works to well. If what I'm thinking is true though then I do have a much clearer picture on how it fits together. So is asp the way to go with this or should I learn php/python/perl/etc... (all that other crap that I don't know that is big with web development.) I see a lot of sites that use php so I'm a little confused on what to do. (ofcourse this should be independent of the database itself? I could design the "front end" in asp and later in php and it should still work the same(excluding the differences due to asp and php)? Thanks, Jon
[quoted text, click to view]
Jon Slaughter wrote: > Ok, so its up to the asp front end to manage security(I guess its better to > say permission) rights? When the user logs onto the web site and interfaces > with the Asp code it will decide what how to handle what the user is able to > do? > > So ASP has its own log in onto the SQL Database(its own pipe so to speak) > but ASP will deal with restricting the users access? So maybe jblow123 can > change his own information but cannot change others because the ASP front > end is designed to only bring up his own information. > > e.g., I'm thinking of an example where jblow wants to view his own account > information. You design some ASP code that will bring up only jblow's > information when he requests it(he can't request others information(or > atleast private information) so only way he could mess with others stuff is > if the ASP code was buggy/insecure? > > Basically your telling me that jblow doesn't access the SQL server/database > directly like I can when I go write an SQL statement and run it in visual > studio? So I get to choose what he can do and what he can't? Since he > cannot really get at the ASP code(?) he can't change things and force it to > see something he's not suppose to? (like trick ASP into thinking he's > jane431 to get here private info?) Correct. Also google "SQL injection" (the short version is that you have to sanity-check all user input to make sure a hacker can't sneak extra SQL code through it). [quoted text, click to view] > So is asp the way to go with this or should I learn php/python/perl/etc... > (all that other crap that I don't know that is big with web development.) I > see a lot of sites that use php so I'm a little confused on what to do. > (ofcourse this should be independent of the database itself? I could design > the "front end" in asp and later in php and it should still work the > same(excluding the differences due to asp and php)?
[quoted text, click to view]
On Feb 9, 4:10 pm, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote: > "Ed Murphy" <emurph...@socal.rr.com> wrote in message > > news:45ccdb5f$0$24503$4c368faf@roadrunner.com... > > > > > Jon Slaughter wrote: > > >>> In SQL Server you have security "groups" and users can join one or > >>> more groups. So a certain group may have read + write access over one > >>> table while another group only has read access. I do not know if this > >>> is how it works in MySQL or not. To accomplish your goal of users > >>> having access to only certain rows in a table is going to require some > >>> server-side code outside of the group security to determine if the > >>> user should be able to access a certain row. > > >> Ok, but what is this code? Is it html, javascript, or what? is it SQL > >> statements that are embedded in the code(Sorta like how I can use SQL in > >> C# but its just more like a simple wrapper that forwards the SQL > >> statements to the server)? > > > ASP, in your case. This may be as simple as including UserID as a > > column in the table, then forwarding SQL statements like: > > > select (list of fields) > > from the_table > > where UserID = 'jblow123' (and possibly other conditions) > > > The overall architecture looks like this: > > > SQL <-------------------- ASP <-------------------- end user > > logged into SQL logged into web site > > as "website" as "jblow123" > > > The "website" SQL login can read/write any row in the table. The > > ASP code chooses to read/write only certain rows in response to a > > request from the "jblow123" web site login. > > Ok, so its up to the asp front end to manage security(I guess its better to > say permission) rights? When the user logs onto the web site and interfaces > with the Asp code it will decide what how to handle what the user is able to > do? > > So ASP has its own log in onto the SQL Database(its own pipe so to speak) > but ASP will deal with restricting the users access? So maybe jblow123 can > change his own information but cannot change others because the ASP front > end is designed to only bring up his own information. > > e.g., I'm thinking of an example where jblow wants to view his own account > information. You design some ASP code that will bring up only jblow's > information when he requests it(he can't request others information(or > atleast private information) so only way he could mess with others stuff is > if the ASP code was buggy/insecure? > > Basically your telling me that jblow doesn't access the SQL server/database > directly like I can when I go write an SQL statement and run it in visual > studio? So I get to choose what he can do and what he can't? Since he > cannot really get at the ASP code(?) he can't change things and force it to > see something he's not suppose to? (like trick ASP into thinking he's > jane431 to get here private info?) > > If this is the case then I suppose its not that difficult. I just have to > learn ASP and SQL now ;) Basically the information and the security are > handled seperately. I think maybe I now have a mental map of what is going > on. Its still kinda fuzzy but after I start working with asp a little in Web > Developer I'll probably get a better feel. I really just have no clue what > asp is and how it works yet and thats probably why I don't understand how it > works to well. If what I'm thinking is true though then I do have a much > clearer picture on how it fits together. > > So is asp the way to go with this or should I learn php/python/perl/etc... > (all that other crap that I don't know that is big with web development.) I > see a lot of sites that use php so I'm a little confused on what to do. > (ofcourse this should be independent of the database itself? I could design > the "front end" in asp and later in php and it should still work the > same(excluding the differences due to asp and php)? > > Thanks, > Jon Whether to use ASP or PHP/Perl is a completely different argument with large crowds on both sides. Personally I would say that if you know C# and you are familiar with .NET, stick with ASP. It is different, but that's because it's a scripting language. You almost have to put your mind in a different state for it. The direction you are going with the web front end controlling what users can do is what I was thinking. I don't know if it's what a database expert would recommend, but anytime I've needed functionality similar to what you described I found I can code a middle-tier to the system easier than anything else. If you think security will be a concern I would listen to what Ed mentioned. SQL injection attacks can be nasty. The biggest design choice you should pay attention to is constrict every field you can to not accept nulls unless they are really needed. I know there are many many webinars on MSDN discussing various security concerns in SQL Server. You're just gonna have to balance how much you need with how much time you have to take care of it all. Good luck. Hope I didn't make your problem any more confusing.
On Feb 12, 10:34 am, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com>
[quoted text, click to view] wrote:
> "justin" <justin.cre...@gmail.com> wrote in message > > news:1171286737.478866.166530@m58g2000cwm.googlegroups.com... > > > > > On Feb 9, 4:10 pm, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote: > >> "Ed Murphy" <emurph...@socal.rr.com> wrote in message > > >>news:45ccdb5f$0$24503$4c368faf@roadrunner.com... > > >> > Jon Slaughter wrote: > > >> >>> In SQL Server you have security "groups" and users can join one or > >> >>> more groups. So a certain group may have read + write access over one > >> >>> table while another group only has read access. I do not know if this > >> >>> is how it works in MySQL or not. To accomplish your goal of users > >> >>> having access to only certain rows in a table is going to require > >> >>> some > >> >>> server-side code outside of the group security to determine if the > >> >>> user should be able to access a certain row. > > >> >> Ok, but what is this code? Is it html, javascript, or what? is it SQL > >> >> statements that are embedded in the code(Sorta like how I can use SQL > >> >> in > >> >> C# but its just more like a simple wrapper that forwards the SQL > >> >> statements to the server)? > > >> > ASP, in your case. This may be as simple as including UserID as a > >> > column in the table, then forwarding SQL statements like: > > >> > select (list of fields) > >> > from the_table > >> > where UserID = 'jblow123' (and possibly other conditions) > > >> > The overall architecture looks like this: > > >> > SQL <-------------------- ASP <-------------------- end user > >> > logged into SQL logged into web site > >> > as "website" as "jblow123" > > >> > The "website" SQL login can read/write any row in the table. The > >> > ASP code chooses to read/write only certain rows in response to a > >> > request from the "jblow123" web site login. > > >> Ok, so its up to the asp front end to manage security(I guess its better > >> to > >> say permission) rights? When the user logs onto the web site and > >> interfaces > >> with the Asp code it will decide what how to handle what the user is able > >> to > >> do? > > >> So ASP has its own log in onto the SQL Database(its own pipe so to speak) > >> but ASP will deal with restricting the users access? So maybe jblow123 > >> can > >> change his own information but cannot change others because the ASP front > >> end is designed to only bring up his own information. > > >> e.g., I'm thinking of an example where jblow wants to view his own > >> account > >> information. You design some ASP code that will bring up only jblow's > >> information when he requests it(he can't request others information(or > >> atleast private information) so only way he could mess with others stuff > >> is > >> if the ASP code was buggy/insecure? > > >> Basically your telling me that jblow doesn't access the SQL > >> server/database > >> directly like I can when I go write an SQL statement and run it in visual > >> studio? So I get to choose what he can do and what he can't? Since he > >> cannot really get at the ASP code(?) he can't change things and force it > >> to > >> see something he's not suppose to? (like trick ASP into thinking he's > >> jane431 to get here private info?) > > >> If this is the case then I suppose its not that difficult. I just have to > >> learn ASP and SQL now ;) Basically the information and the security are > >> handled seperately. I think maybe I now have a mental map of what is > >> going > >> on. Its still kinda fuzzy but after I start working with asp a little in > >> Web > >> Developer I'll probably get a better feel. I really just have no clue > >> what > >> asp is and how it works yet and thats probably why I don't understand how > >> it > >> works to well. If what I'm thinking is true though then I do have a much > >> clearer picture on how it fits together. > > >> So is asp the way to go with this or should I learn > >> php/python/perl/etc... > >> (all that other crap that I don't know that is big with web development.) > >> I > >> see a lot of sites that use php so I'm a little confused on what to do. > >> (ofcourse this should be independent of the database itself? I could > >> design > >> the "front end" in asp and later in php and it should still work the > >> same(excluding the differences due to asp and php)? > > >> Thanks, > >> Jon > > > Whether to use ASP or PHP/Perl is a completely different argument with > > large crowds on both sides. Personally I would say that if you know C# > > and you are familiar with .NET, stick with ASP. It is different, but > > that's because it's a scripting language. You almost have to put your > > mind in a different state for it. > > The problem I'm having is that to use ASP I have to use a server that > handles ASP. Right now my web host doesn't allow it except if I pay twice as > much. What I'm worried about is that if I learn it I might not actually be > able to use it much. I'm also worried that it would be better to just ajax > and jsp or something like that since maybe it is better supported? I really > think I would like asp(because I like C# and .NET) but it seems to be > expensive and limiting. > > > The direction you are going with the web front end controlling what > > users can do is what I was thinking. I don't know if it's what a > > database expert would recommend, but anytime I've needed functionality > > similar to what you described I found I can code a middle-tier to the > > system easier than anything else. > > But I still need some client side execution ;/ I think I got a good picture > now what to do on the server side but I don't know how to actually fuse the > two together to get what I want in a "secure" way. It seems that I might > have to use java to do the client side which will somehow interface with the > server side front end(not directly with the database). > > > If you think security will be a concern I would listen to what Ed > > mentioned. SQL injection attacks can be nasty. The biggest design > > choice you should pay attention to is constrict every field you can to > > not accept nulls unless they are really needed. I know there are many > > many webinars on MSDN discussing various security concerns in SQL > > Server. You're just gonna have to balance how much you need with how > > much time you have to take care of it all. > > I am going to try and avoid the client having any direct contact with the > SQL server so I shouldn't have to worry about this? He won't see any SQL > code in the page sources or anything like that so he won't know where the > SQL server is or be able to modify any SQL code in the page source? > > > Good luck. Hope I didn't make your problem any more confusing. >
[quoted text, click to view]
Jon Slaughter wrote: >> If you think security will be a concern I would listen to what Ed >> mentioned. SQL injection attacks can be nasty. The biggest design >> choice you should pay attention to is constrict every field you can to >> not accept nulls unless they are really needed. I know there are many >> many webinars on MSDN discussing various security concerns in SQL >> Server. You're just gonna have to balance how much you need with how >> much time you have to take care of it all. >> > > I am going to try and avoid the client having any direct contact with the > SQL server so I shouldn't have to worry about this? He won't see any SQL > code in the page sources or anything like that so he won't know where the > SQL server is or be able to modify any SQL code in the page source? SQL injection typically involves a hacker entering something like the following into a data field: '; select * from users -- and hoping that your ASP/PHP/whatever code will blindly plug that into a SQL statement, something like the following: select field1, field2 from items where description like '%s' Even if it doesn't work directly, it may generate some error messages that let the hacker figure out part of your database structure. Another angle of attack is to try injecting this: ' or 1 = 1 -- into a password field, and hoping that the code will blindly plug it into something like this:
[quoted text, click to view]
Jon Slaughter wrote: > I see. So is there any standard way around this? Surely there should be a > way to tell SQL to treat the a string as a literal. (anything enclodes a > part of $'s are treated literally and not parsed) Would this solve the > problem? The issue mainly seems one of parsing but surely that is easy to > get around by telling the parser not to parse certain portions that you know > to be literals? > > I'm not sure how I'm suppose to handle the above in general? Obivously I can > parse the string for "'"'s and remove them or report some error but will > that fix all the issues? > > it would be nice if something like this > > select user from users where user = $%s$ and password = hash($%s$) > > would make sure that %s was not parsed but treated as a literal? Here, just read this:
[quoted text, click to view] "justin" <justin.creasy@gmail.com> wrote in message news:1171286737.478866.166530@m58g2000cwm.googlegroups.com... > On Feb 9, 4:10 pm, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote: >> "Ed Murphy" <emurph...@socal.rr.com> wrote in message >> >> news:45ccdb5f$0$24503$4c368faf@roadrunner.com... >> >> >> >> > Jon Slaughter wrote: >> >> >>> In SQL Server you have security "groups" and users can join one or >> >>> more groups. So a certain group may have read + write access over one >> >>> table while another group only has read access. I do not know if this >> >>> is how it works in MySQL or not. To accomplish your goal of users >> >>> having access to only certain rows in a table is going to require >> >>> some >> >>> server-side code outside of the group security to determine if the >> >>> user should be able to access a certain row. >> >> >> Ok, but what is this code? Is it html, javascript, or what? is it SQL >> >> statements that are embedded in the code(Sorta like how I can use SQL >> >> in >> >> C# but its just more like a simple wrapper that forwards the SQL >> >> statements to the server)? >> >> > ASP, in your case. This may be as simple as including UserID as a >> > column in the table, then forwarding SQL statements like: >> >> > select (list of fields) >> > from the_table >> > where UserID = 'jblow123' (and possibly other conditions) >> >> > The overall architecture looks like this: >> >> > SQL <-------------------- ASP <-------------------- end user >> > logged into SQL logged into web site >> > as "website" as "jblow123" >> >> > The "website" SQL login can read/write any row in the table. The >> > ASP code chooses to read/write only certain rows in response to a >> > request from the "jblow123" web site login. >> >> Ok, so its up to the asp front end to manage security(I guess its better >> to >> say permission) rights? When the user logs onto the web site and >> interfaces >> with the Asp code it will decide what how to handle what the user is able >> to >> do? >> >> So ASP has its own log in onto the SQL Database(its own pipe so to speak) >> but ASP will deal with restricting the users access? So maybe jblow123 >> can >> change his own information but cannot change others because the ASP front >> end is designed to only bring up his own information. >> >> e.g., I'm thinking of an example where jblow wants to view his own >> account >> information. You design some ASP code that will bring up only jblow's >> information when he requests it(he can't request others information(or >> atleast private information) so only way he could mess with others stuff >> is >> if the ASP code was buggy/insecure? >> >> Basically your telling me that jblow doesn't access the SQL >> server/database >> directly like I can when I go write an SQL statement and run it in visual >> studio? So I get to choose what he can do and what he can't? Since he >> cannot really get at the ASP code(?) he can't change things and force it >> to >> see something he's not suppose to? (like trick ASP into thinking he's >> jane431 to get here private info?) >> >> If this is the case then I suppose its not that difficult. I just have to >> learn ASP and SQL now ;) Basically the information and the security are >> handled seperately. I think maybe I now have a mental map of what is >> going >> on. Its still kinda fuzzy but after I start working with asp a little in >> Web >> Developer I'll probably get a better feel. I really just have no clue >> what >> asp is and how it works yet and thats probably why I don't understand how >> it >> works to well. If what I'm thinking is true though then I do have a much >> clearer picture on how it fits together. >> >> So is asp the way to go with this or should I learn >> php/python/perl/etc... >> (all that other crap that I don't know that is big with web development.) >> I >> see a lot of sites that use php so I'm a little confused on what to do. >> (ofcourse this should be independent of the database itself? I could >> design >> the "front end" in asp and later in php and it should still work the >> same(excluding the differences due to asp and php)? >> >> Thanks, >> Jon > > Whether to use ASP or PHP/Perl is a completely different argument with > large crowds on both sides. Personally I would say that if you know C# > and you are familiar with .NET, stick with ASP. It is different, but > that's because it's a scripting language. You almost have to put your > mind in a different state for it. > The problem I'm having is that to use ASP I have to use a server that handles ASP. Right now my web host doesn't allow it except if I pay twice as much. What I'm worried about is that if I learn it I might not actually be able to use it much. I'm also worried that it would be better to just ajax and jsp or something like that since maybe it is better supported? I really think I would like asp(because I like C# and .NET) but it seems to be expensive and limiting. [quoted text, click to view] > The direction you are going with the web front end controlling what > users can do is what I was thinking. I don't know if it's what a > database expert would recommend, but anytime I've needed functionality > similar to what you described I found I can code a middle-tier to the > system easier than anything else. > But I still need some client side execution ;/ I think I got a good picture now what to do on the server side but I don't know how to actually fuse the two together to get what I want in a "secure" way. It seems that I might have to use java to do the client side which will somehow interface with the server side front end(not directly with the database). [quoted text, click to view] > If you think security will be a concern I would listen to what Ed > mentioned. SQL injection attacks can be nasty. The biggest design > choice you should pay attention to is constrict every field you can to > not accept nulls unless they are really needed. I know there are many > many webinars on MSDN discussing various security concerns in SQL > Server. You're just gonna have to balance how much you need with how > much time you have to take care of it all. > I am going to try and avoid the client having any direct contact with the SQL server so I shouldn't have to worry about this? He won't see any SQL code in the page sources or anything like that so he won't know where the SQL server is or be able to modify any SQL code in the page source? [quoted text, click to view] > Good luck. Hope I didn't make your problem any more confusing. > No, It helped. I have a much clearer idea about whats going on but I still having those issues above. I'm not sure what to do but I guess I'm just going to have to dive into it to get started. The basic web front end should be quite easy as its just a database gui like thing(essentially
[quoted text, click to view] "justin" <justin.creasy@gmail.com> wrote in message
news:1171298259.615820.10320@k78g2000cwa.googlegroups.com... > On Feb 12, 10:34 am, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> > wrote: >> "justin" <justin.cre...@gmail.com> wrote in message >> >> news:1171286737.478866.166530@m58g2000cwm.googlegroups.com... >> >> >> >> > On Feb 9, 4:10 pm, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote: >> >> "Ed Murphy" <emurph...@socal.rr.com> wrote in message >> >> >>news:45ccdb5f$0$24503$4c368faf@roadrunner.com... >> >> >> > Jon Slaughter wrote: >> >> >> >>> In SQL Server you have security "groups" and users can join one or >> >> >>> more groups. So a certain group may have read + write access over >> >> >>> one >> >> >>> table while another group only has read access. I do not know if >> >> >>> this >> >> >>> is how it works in MySQL or not. To accomplish your goal of users >> >> >>> having access to only certain rows in a table is going to require >> >> >>> some >> >> >>> server-side code outside of the group security to determine if the >> >> >>> user should be able to access a certain row. >> >> >> >> Ok, but what is this code? Is it html, javascript, or what? is it >> >> >> SQL >> >> >> statements that are embedded in the code(Sorta like how I can use >> >> >> SQL >> >> >> in >> >> >> C# but its just more like a simple wrapper that forwards the SQL >> >> >> statements to the server)? >> >> >> > ASP, in your case. This may be as simple as including UserID as a >> >> > column in the table, then forwarding SQL statements like: >> >> >> > select (list of fields) >> >> > from the_table >> >> > where UserID = 'jblow123' (and possibly other conditions) >> >> >> > The overall architecture looks like this: >> >> >> > SQL <-------------------- ASP <-------------------- end user >> >> > logged into SQL logged into web site >> >> > as "website" as "jblow123" >> >> >> > The "website" SQL login can read/write any row in the table. The >> >> > ASP code chooses to read/write only certain rows in response to a >> >> > request from the "jblow123" web site login. >> >> >> Ok, so its up to the asp front end to manage security(I guess its >> >> better >> >> to >> >> say permission) rights? When the user logs onto the web site and >> >> interfaces >> >> with the Asp code it will decide what how to handle what the user is >> >> able >> >> to >> >> do? >> >> >> So ASP has its own log in onto the SQL Database(its own pipe so to >> >> speak) >> >> but ASP will deal with restricting the users access? So maybe jblow123 >> >> can >> >> change his own information but cannot change others because the ASP >> >> front >> >> end is designed to only bring up his own information. >> >> >> e.g., I'm thinking of an example where jblow wants to view his own >> >> account >> >> information. You design some ASP code that will bring up only jblow's >> >> information when he requests it(he can't request others information(or >> >> atleast private information) so only way he could mess with others >> >> stuff >> >> is >> >> if the ASP code was buggy/insecure? >> >> >> Basically your telling me that jblow doesn't access the SQL >> >> server/database >> >> directly like I can when I go write an SQL statement and run it in >> >> visual >> >> studio? So I get to choose what he can do and what he can't? Since >> >> he >> >> cannot really get at the ASP code(?) he can't change things and force >> >> it >> >> to >> >> see something he's not suppose to? (like trick ASP into thinking he's >> >> jane431 to get here private info?) >> >> >> If this is the case then I suppose its not that difficult. I just have >> >> to >> >> learn ASP and SQL now ;) Basically the information and the security >> >> are >> >> handled seperately. I think maybe I now have a mental map of what is >> >> going >> >> on. Its still kinda fuzzy but after I start working with asp a little >> >> in >> >> Web >> >> Developer I'll probably get a better feel. I really just have no clue >> >> what >> >> asp is and how it works yet and thats probably why I don't understand >> >> how >> >> it >> >> works to well. If what I'm thinking is true though then I do have a >> >> much >> >> clearer picture on how it fits together. >> >> >> So is asp the way to go with this or should I learn >> >> php/python/perl/etc... >> >> (all that other crap that I don't know that is big with web >> >> development.) >> >> I >> >> see a lot of sites that use php so I'm a little confused on what to >> >> do. >> >> (ofcourse this should be independent of the database itself? I could >> >> design >> >> the "front end" in asp and later in php and it should still work the >> >> same(excluding the differences due to asp and php)? >> >> >> Thanks, >> >> Jon >> >> > Whether to use ASP or PHP/Perl is a completely different argument with >> > large crowds on both sides. Personally I would say that if you know C# >> > and you are familiar with .NET, stick with ASP. It is different, but >> > that's because it's a scripting language. You almost have to put your >> > mind in a different state for it. >> >> The problem I'm having is that to use ASP I have to use a server that >> handles ASP. Right now my web host doesn't allow it except if I pay twice >> as >> much. What I'm worried about is that if I learn it I might not actually >> be >> able to use it much. I'm also worried that it would be better to just >> ajax >> and jsp or something like that since maybe it is better supported? I >> really >> think I would like asp(because I like C# and .NET) but it seems to be >> expensive and limiting. >> >> > The direction you are going with the web front end controlling what >> > users can do is what I was thinking. I don't know if it's what a >> > database expert would recommend, but anytime I've needed functionality >> > similar to what you described I found I can code a middle-tier to the >> > system easier than anything else. >> >> But I still need some client side execution ;/ I think I got a good >> picture >> now what to do on the server side but I don't know how to actually fuse >> the >> two together to get what I want in a "secure" way. It seems that I might >> have to use java to do the client side which will somehow interface with >> the >> server side front end(not directly with the database). >> >> > If you think security will be a concern I would listen to what Ed >> > mentioned. SQL injection attacks can be nasty. The biggest design >> > choice you should pay attention to is constrict every field you can to >> > not accept nulls unless they are really needed. I know there are many >> > many webinars on MSDN discussing various security concerns in SQL >> > Server. You're just gonna have to balance how much you need with how
[quoted text, click to view] "Ed Murphy" <emurphy42@socal.rr.com> wrote in message news:45d0c014$0$1380$4c368faf@roadrunner.com... > Jon Slaughter wrote: > >>> If you think security will be a concern I would listen to what Ed >>> mentioned. SQL injection attacks can be nasty. The biggest design >>> choice you should pay attention to is constrict every field you can to >>> not accept nulls unless they are really needed. I know there are many >>> many webinars on MSDN discussing various security concerns in SQL >>> Server. You're just gonna have to balance how much you need with how >>> much time you have to take care of it all. >>> >> >> I am going to try and avoid the client having any direct contact with the >> SQL server so I shouldn't have to worry about this? He won't see any SQL >> code in the page sources or anything like that so he won't know where the >> SQL server is or be able to modify any SQL code in the page source? > > SQL injection typically involves a hacker entering something like the > following into a data field: > > '; select * from users -- > > and hoping that your ASP/PHP/whatever code will blindly plug that into > a SQL statement, something like the following: > > select field1, field2 from items where description like '%s' > > Even if it doesn't work directly, it may generate some error messages > that let the hacker figure out part of your database structure. > > Another angle of attack is to try injecting this: > > ' or 1 = 1 -- > > into a password field, and hoping that the code will blindly plug it > into something like this: > > select user from users where user = '%s' and password = hash('%s') I see. So is there any standard way around this? Surely there should be a way to tell SQL to treat the a string as a literal. (anything enclodes a part of $'s are treated literally and not parsed) Would this solve the problem? The issue mainly seems one of parsing but surely that is easy to get around by telling the parser not to parse certain portions that you know to be literals? I'm not sure how I'm suppose to handle the above in general? Obivously I can parse the string for "'"'s and remove them or report some error but will that fix all the issues? it would be nice if something like this select user from users where user = $%s$ and password = hash($%s$) would make sure that %s was not parsed but treated as a literal?
Don't see what you're looking for? Try a search.
|
March 2003
April 2003
May 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |