What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

I have a pretty active discussion on SO right now, about giving an answer, that could potentially harm the questioner. The question is Do I have to escape sql command when ajax-ing it? and discussion is in comments to my answer.

In short, the OP asks for a way to implement a really dangerous thing. I've provided an answer to the question with a lot of warnings, while Stephen told me that this is a very bad idea, and it is better to use prepared statements, and so on. He is absolutely right; I'm among those who upvoted his answer, but the question remains.

So, I would like to hear community's opinion on that topic. Should we protect others from shooting in their legs, or let them have responsibility to do it, if they want?

Update: One of my university teachers created sql-ex.ru site (long ago). The idea of that site is to allow people training their SQL skills. I won't tell much about the site, you can register and check it out yourself (it is available in english), but the whole idea is just the same: execute SQL provided by the user. Of course it is fltered, checked that it is safe, placed in a sandbox, etc. But the concept is just the same. It's rather popular resource, it is on third place in most popular russian search engine and have large and growing community. I hope this update will stimulate further discussion.

share|improve this question
1  
To take the words out of Stephen's mouth, it can be irresponsible and is at your own discretion to make that call; but that really depends on how much of a responsibility you feel you have as a competent developer in your industry. And just to note on your clarification of not letting evil people use the code, I'd hazard to say make sure people don't use it, period - we're error prone. –  Grant Thomas Aug 16 '11 at 9:15
    
The original question was mine and I am very happy that @J0HN answered. I do understand the security implications and don't find my reasons as excuses. It happened already few times that I asked for something was given better solution that I had in mind I tried to code by myself. Always happy to learn and listen to others. –  Radek Aug 16 '11 at 9:21
    
My reason is that I am a tester and I restore (overwrite) databases few times a day. Sometimes I need to quickly insert,select,update something. So far in current version of my web based tool that was helping me for months (and lately even other co-workers started to use - let's say real programmer - which I don't count myself as) there are features that do drop whole database, update configuration of the db, changes email addresses for all users - so I don't accidentally sent an email to the real user. No I find handy to be able quickly run any sql command on given db.. –  Radek Aug 16 '11 at 9:28
    
Do you have better solution for that? I am happy to use it :-) –  Radek Aug 16 '11 at 9:29
    
@Radeck: There are a number of solutions to the problem you just stated, which as far as I can tell is the real problem you want to solve. For starters, you could use an SQL client. –  Wesley Murch Aug 16 '11 at 9:31
2  
@Radek MSSQL Management Studio, or your database engine equivalent. –  Grant Thomas Aug 16 '11 at 9:32
    
@Wesley: what is the solution? I am happy to implement anything if it does what I want, what needs to be done. I am using proper sql client but my solution is going to be easier and faster to use. I do not care how it is going to be done but if I can do what I want to do. –  Radek Aug 16 '11 at 9:36
    
@Radeck: If you can explain your situation and requirements clearly, as well as what you are doing now to manage your DB and why it's not working out for you, opening a new question would be to your benefit. –  Wesley Murch Aug 16 '11 at 9:38
    
@Mr. Disappointment: I use web base client and dedicated client (razor) to access db2. I know that my solution will require less work for me, less time. I cannot see any issues with security here. –  Radek Aug 16 '11 at 9:39
    
@Wesley: That's what I did :-) I posted my question and got an answer ( I haven't implemented it yet though ). I am very happy with the answer and if it works, will be accepted and used every day soooooooooo many times. PS. My name is Radek. It's ok you don't have to apologize. ;-) –  Radek Aug 16 '11 at 9:45
3  
@Radek, actually you'd better really not use it. And don't accept it, because other people may just use it, without knowing that in your case you have very specific use-case, your system is not intended to work in a wild, resides under heavy protection and strict firewalls, etc. Also, people, if you are going to discuss the question itself, you can do it in SO :) –  J0HN Aug 16 '11 at 9:49
    
@J0HN: really, why it would be better for me not to use it? It is like suggesting Don't use knife you can cut yourself. –  Radek Aug 16 '11 at 10:05
2  
@Radek: It's more like asking how to get the wrong tool to work instead of asking which tool you should use. –  Wesley Murch Aug 16 '11 at 10:17
2  
Re sql-ex.ru: There is a huge difference between a training site (with training databases) designed to allow users to run some SQL queries, and building a security hole into an application. I don't think that is a good example. –  Pëkka Aug 16 '11 at 17:35
1  
@Eat sql-ex.ru is just an example of very successful usage of exactly the same concept: execute user input as sql query. Just like PhpMyAdmib, btw. –  J0HN Aug 17 '11 at 5:00
show 2 more comments

10 Answers

up vote 33 down vote accepted

Should we protect others from shooting in their legs, or let them have responsibility to do it, if they want?

In the end, people are free to do whatever they want, including shooting themselves in the leg.

However, after two years of SO experience, I have come to refrain from posting code that is dangerous. The risk of it getting copy + pasted, or used by someone who doesn't speak english well and can't understand the warnings, is too great.

In my opinion, if you have code that solves the OP's problem but you think shouldn't be used, you shouldn't post it.

share|improve this answer
22  
I think "Don't post code you wouldn't use" is kind of a Golden Rule in any case. –  Wesley Murch Aug 16 '11 at 9:18
    
So you won't let anybody make mistakes? And learn from them? –  Lightness Races in Orbit Aug 17 '11 at 6:41
1  
@Tomalak I don't mind if others see fit to provide the code for a purpose I think is extremely stupid or dangerous (unless it's something really destructive, rm -rf / style), but I usually won't do it myself. You make fair points in your answer, but the question in its first incarnation had no notion of a specific phpMyAdmin-like use case. It looked more like "I want to be able to send full queries because properly parsing them is hard, and I'm the only person who will ever use this." And that is an argument for introducing a security hole that I can't support - from painful experience –  Pëkka Aug 17 '11 at 8:03
1  
completely unrelated side-note: the specific command rm -rf / is completely safe on a reasonably up-to-date Linux, because rm special-cases the root /. Now that should give the "don't post dangerous stuff" group something to talk about ;-) –  Joachim Sauer Aug 17 '11 at 8:16
    
@Eat: Understandable. –  Lightness Races in Orbit Aug 17 '11 at 9:56
    
@tomalak - it isn't about 'letting' others make mistakes. It's about your own personal choices as to whether or not you aid and abet them if thy choose to do so. –  RobM Aug 17 '11 at 10:32
    
@Joachim - so that makes the behaviour of a potentially dangerous command unpredictable depending on what Unix/Linux variant and patch level you're sitting in front of. Who the hell thought that introducing unpredictable behaviour was a good idea. –  RobM Aug 17 '11 at 10:33
2  
@Robert: according to that reasoning you must never change the behaviour of any command, even if its considered a bug, because then it would be "unpredictable" if the bug occurs or not. In this case I'm fairly in the camp that votes for the change: there is absolutely no real and relevant use case for doing rm -rf /. –  Joachim Sauer Aug 17 '11 at 10:36
    
@Joachim +1 for that comment because I see what you mean, but I was thinking that the changes should be backported to (within reason) older versions too, and that hopefully its easy to configure this behaviour if I can't upgrade older boxen and inconsistency worries me more than the other factors. –  RobM Aug 17 '11 at 10:40
    
@Robert: "It's about your own personal choices as to whether or not you aid and abet them if thy choose to do so" Yes, it is... on SO. On Meta we're having a discussion about why. :) –  Lightness Races in Orbit Aug 18 '11 at 8:52
1  
I find this opinion to be most peculiar. The mentality here at SO-etc, is like that of a control-freak nanny state. Next you'll be scanning answers for keywords such as delete, rm -Rf, format. Get a life guys! –  Tom Jones Aug 18 '11 at 9:32
    
@Tom yeah man. Next thing, they'll come to people's houses! –  Pëkka Aug 18 '11 at 11:30
add comment

I find this whole discussion fairly amusing, if we were to listen to the haters http://data.stackexchange.com would be disallowed.

SQL is shipped to SQL Server to run via json, which is exactly what the asker asked.

Sure, its a read only account, running on an isolated SQL Server instance on an isolated machine with heavy caching, throttling and even automatic banning ... but still ... SQL via ajax.


I do not think any policy can be generalized here, sometimes dangerous is an acceptable answer, sometimes it is not. It all totally depends on a huge set of circumstances.

In general I would not be offering newbies instruction that are most likely going to lead to carnage, but the right question from the right person deserves to be answered.

share|improve this answer
add comment

"You can't imagine any use case for sending raw SQL statements over a network? Never used SQLYog or PhpMyAdmin? I wonder whether the developers of those tools had to put up with this level of utterly useless repetitive drivel as well." - I clearly stated that if your application is the EXCEPTION to the "don't do SQL client side" rule, you should be VERY experienced with the whole stack (i.e. any technology you're using), and thus not asking what is essentially a beginners question about URL encoding.

As for my evaluation of the OP's skills - I made a judgement based on a) the way the question was asked b) the complexity of the question and I was absolutely correct. The OP states himself: "My reason is that I am a tester and I restore (overwrite) databases few times a day. Sometimes I need to quickly insert,select,update something."

The OP needs an SQL client and a demo on how to import a pre-written SQL script, plain and simple.

share|improve this answer
    
This is exactly how I feel about it, giving dangerous tools to an inexperienced developer is irresponsible. If the OP was in fact experienced enough to truly understand the implications of the feature, the simplistic question about how to correctly deploy it would have not have been asked, and the appropriate tool (an SQL client perhaps) would be preferred instead in the first place. –  Wesley Murch Aug 19 '11 at 22:54
add comment

Stack Overflow is a Q&A website. There was a Q, and this was the correct A to that question.

In addition, as a safeguard, the answer was very clear about the issues, complexities and considerations with the entire scenario.

Let's sum that up:

  • Answered the question
  • Provided sound advice
  • Helped the OP (in either case)

Seems like a good answer to me.

Had the answerer not made it clear that the scenario is dangerous then things would be different, but I'm getting a bit sick of the horrendous amount of noise generated around questions like that by people repeating themselves over and over again, not answering the question posed, when for all you know the OP is already well aware of the facts. Let the answer contain the appropriate warnings, and move on.

What's that? You can't imagine any use case for sending raw SQL statements over a network? Never used SQLYog or PhpMyAdmin? I wonder whether the developers of those tools had to put up with this level of utterly useless repetitive drivel as well.


Update 1

I pointed out that in order for this application to even be dangerous, the account under which the query runs would have to have more than SELECT permissions in the first place.

A concern from some other users was that, were this the case, the OP wouldn't be asking the question.

I find this slightly offensive. You've jumped to the baseless conclusion, essentially, that the OP is "stupid". What does MySQL permissions have to do with AJAX escaping? How can you gauge the OP's skills with one based on having posted a question about the other?

And surely, then, the most helpful approach is to include this information regarding permissions in your answer, rather than entertaining debates in the comments and not providing the answer.


Update 2

"Sometimes you have to distinguish between what OP is asking, and what he/she really wants to happen." I neither agree nor disagree in particular, but I do want to say this:

SO has become a cross between a support site for helping one user at a time (with their particular use case, which may end up differing from their stated requirements, as you suggest), and creating an archive of Questions and Answers. In the latter context, it's not a terribly helpful site when the answers don't actually answer the stated question. I've always been a bit torn between these two interpretations.

share|improve this answer
    
+1 for PhpMyadmin example :) –  J0HN Aug 17 '11 at 4:58
    
What exactly is "utterly useless repetitive drivel" supposed to be referring to? –  Wesley Murch Aug 17 '11 at 12:34
1  
@Wesley: It's an ironically exaggerated response to the fact that the community is not content to write a warning and/or refuse to answer when a "dangerous" question is posted; ten or twenty identical "don't do this" "why are you doing this" "what is wrong with you" comments must be posted instead. It ultimately becomes noise and, I think, detracts from the message that it's all trying to convey. –  Lightness Races in Orbit Aug 17 '11 at 12:50
    
Repetitive: sometimes. Drivel: possibly. Useless? Definitely not. I don't see how 30 people saying "Don't do this" detracts from the message at all, in fact I would think the opposite. Sure it's not all necessary, two or three comments should be plenty. And there is a big difference between PhpMyAdmin and an application built by a single programmer with questionable knowledge in security practices. –  Wesley Murch Aug 17 '11 at 12:54
    
@Wesley: As I said, it was ironically exaggerated in some respects. I like alliteration. –  Lightness Races in Orbit Aug 17 '11 at 13:00
    
An application built by a single programmer would still be better than what is being discussed here - this is a TESTER who wants to try his hand at writing code rather than use an existing SQL Client. –  Stephen Aug 18 '11 at 14:06
add comment

Ok, I said I wouldn't join the discusson, but I will:

We should always consider the alternatives:

Assuming for a moment that the SO community collectively comes to the conclusions that we shouldn't answer "How do I shoot myself in the foot?"-style question with "solutions".

This will result in many answers saying "don't shoot yourself in the foot, because ..." (and possibly a few who don't follow the collective conclusion, but let's assume those don't exist for now). That's all sound advice and if the questioner (and anyone else who reads the answers later on) follows it, everything is peachy.

Now fresh, unexperienced developer X has the same problem. He goes to the Googles and finds this question. He quickly scans it for code and doesn't find any that helps him. Discouraged, he leaves and posts the question on answers.yahoo.com where a helpful fellow (who doesn't know what SO is and had no chance to see this discussion) comes along and posts the complete shoot_myself_in_the_foot(boolean lethal) method for developer X to copy.

Result: Developer X is happy until he shoots himself in the foot in a production environment.

Maybe we should provide the how-to for shooting yourself in the foot in the safest possible way together with adequate reasoning on why not to shoot yourself in the foot.

share|improve this answer
1  
Another nice one :-) He'll certainly find the answers on w3schools, too –  Lukas Eder Aug 17 '11 at 12:30
add comment

I don't want to join one side or another in this discussion, but just give one little thing to think about:

Some people here argue that the answer should not be posted based on the perceived competence level of the person asking it.

This has two problems: First, the competence level is perceived. That perception might be accurate (enough) or it might not. Maybe the questioner is much more competent than he seems, but isn't a native english speaker, or there is some other reason for the perception.

The more important problem with this is that the person asking the question is by far not the only one who will use the answer to achieve their goal. A well-written question (or a badly-written, but common one) will get hundreds of views over time. And most of those will be looking for an answer.

So if the only reason you post the answer (or not post the answer) is the perceived competence level of the person asking the question, then I strongly encourage you to reconsider this position.

share|improve this answer
    
Everything is clear except the last paragraph. What do you mean by re-thinking? –  J0HN Aug 17 '11 at 8:25
    
@J0HN: it's a faulty translation. "reconsider" is the correct term here. Fixed. –  Joachim Sauer Aug 17 '11 at 8:28
    
I'm not a native english-speaker, so I had understood what do you meant by that word. :) But I haven't got the sence of it. –  J0HN Aug 17 '11 at 8:33
    
Nicely put. Especially the thing about perception. Prejudice is off-topic on Stack Overflow, too –  Lukas Eder Aug 17 '11 at 12:28
add comment

Actually, it's better they ask the question and they're told about the consequences. If they hadn't asked, someone would sure have found out and caused severe damage eventually.

Besides, with tools such as sqlmap, there's no hope anyway. As soon as you forget one bind variable, inlining parameters instead and your reverse-proxy does not filter anything, you may as well say good-bye to your database or to your sensitive data. And that can happen to anyone.

share|improve this answer
3  
Actually, it's better they ask the question and they're told about the consequences. –  Radek Aug 16 '11 at 10:33
add comment

There are a few different situations here.

Enough information to determine security

If you absolutely know that the answer is insecure under any and all circumstances (internal networks, localhost only access, no internet access, turned off, unplugged, in a locked room, etc) given the question, then yes, downvote it, and PLEASE comment on why.

However, keep in mind that even if the answer is insecure for the given question, future google searches may land on that page and the answer, though insecure for the question itself, may be secure enough for someone searching on those terms that falls into this question.

So even when you know it's insecure to implement a given solution under a particular set of circumstances, it is not wrong to teach someone about possible solutions that may actually have benefit, either as teaching examples, or usable under other situations.

Not enough information to determine security

In most cases we do not have any information regarding the particulars. We cannot infer, then that this is insecure in the particular way they need to use it. Some might claim that since SQL injection is problematic, then we can assume that sql access of any kind is insecure, but the language itself isn't the problem - it's who has access to craft SQL messages the system will use.

Therefore insecurity exists in a possibly gray area, depending on your point of view, but ONLY if you assume quite a bit based on a small amount of information.

So in most situations, where not enough information is provided to determine the ethics, go ahead and post the answer, or upvote the answer that, under some circumstances may be insecure, but cannot be assumed to be bad due to lack of information. A nice disclaimer is good, but not necessary.

So, largely, I'm not in favor of downvoting because your system of security and surrounding assumptions prohibits it.

But the downvote is yours to do with as you please.

If you truly find it objectionable under all circumstances, though, flag it for moderator attention so it can be deleted.

share|improve this answer
    
Adapted from my post at meta.stackexchange.com/questions/36397/… –  Adam Davis Aug 16 '11 at 17:57
    
I love your answer, especially that part ... then yes, downvote it, and PLEASE comment on why. Thank you. –  Radek Aug 16 '11 at 23:16
    
+1. Just add "Don't post code you wouldn't use" from Wesley Murch's comment to Eat more Twisters' answer. –  J0HN Aug 17 '11 at 6:33
add comment

You should by all means warn them, but if they insist on doing the Bad Practice™ then it is their own problem, not yours. We've all done something wrong coding wise, and most of us learn from it. It only becomes your problem when the OP comes back with the question Halp!! Myes Customer tables is gone-berger, iz been hax0red, how do i get it back? Then you will feel a deep pang of regret and want to help the guy again because you feel you didn't warn him enough the first time.

It is also good that you voice your warnings like that, it may help the innumerable other people who read your answer and heed your warning.

In any case, he may have a valid reason for doing this - look at the Stack Exchange Data Explorer - how do you think it gets the query from the edit box in your browser to the database?


Edit:
I notice that Radek (the author of the question being discussed) is following this thread - excellent! And one more thing: DON'T DO IT! Never EVER execute a query verbatim from a client - even on an intranet where you think you can trust the users - no matter which statistics you look at1 a very significant portion of data hacks or breaches come from internal staff.

1 these are just some examples i cherry picked - they all tell the story in a different way yet strangely it is the same - never trust your staff

share|improve this answer
4  
It could become our problem if they actually work on a product we use, or are going to use once the code is "complete". –  Bo Persson Aug 16 '11 at 9:50
1  
Anybody in the office has access to these databases in any way they want to. My thing is just going to offer another way. –  Radek Aug 16 '11 at 10:35
2  
I support Radek on this case. He's made his point about system architecture. I'd recommend he didn't use GET as an HTTP protocol but POST, but why is it absolutely so bad to do it...? If you have Oracle Grid Control running and you don't protect it from the Internet, you have the same situation... –  Lukas Eder Aug 16 '11 at 10:38
    
@Lukas - the keyword i used was verbatim - one should ALWAYS process the user input before running it, to not do so is a massive security risk to your database. Radek's case is a little different in that the databases are already wide open, but this shouldn't be regarded as normal practice and should be discouraged on an open forum like this, which is what this whole conversation was about. If Radek is okay with the dangers of this (which he is), then that is his business. –  slugster Aug 16 '11 at 11:35
2  
I don't think this is a forum. Radek has a clear (clarified) question about a specific use case. As soon as that use case is clear, all lengthy discussions about SQL injection become off topic... –  Lukas Eder Aug 16 '11 at 11:43
    
@slugster: If the user may read any record from the tables on which he has permissions, and the user has only SELECT rights, pray tell what the issue is? And Lukas is right; Stack Overflow is not a forum. –  Lightness Races in Orbit Aug 17 '11 at 6:39
    
@Tom - It seems some may have taken my use of the word forum out of context - i know very well what a forum is, one of the meanings is a place where people meet and it can be loosely applied to SO even though SO doesn't follow the traditional web forum model. Enough already, yeah? –  slugster Aug 17 '11 at 8:16
    
@slugster: "Loosely" is the operative word. The focus of SO is questions and answers, not social interaction; that it can be identified as a place where people meet is frankly incidental. And please answer my question! :) –  Lightness Races in Orbit Aug 17 '11 at 9:55
add comment

Sometimes you have to distinguish between what OP is asking, and what he/she really wants to happen. If you see that they've already chosen a treacherous path, it's up to you to help them back up, and to guide them in the right direction.

In this case, OP wants an easy way to perform SQL queries, and has decided literally run them from the address bar in the browser. Very bad practice, very dangerous. Not something we should be teaching, even if it's for an intranet site, or "no one" will ever use it, or whatever excuse is given. This isn't the kind of information you want someone to come up on after preforming a google search.

A good answer would at the very least warn of the dangers, and discourage dangerous practices. A great answer would do this, and show them a better way to achieve their real goal.

I completely agree with one of the commenters sentiments:

The level of complexity of the question, indicates the OP is not overly experienced, and thus does not truly understand the associated dangers with this approach. Saying "hey by the way this is a really dangerous idea, but hey here is the code to do it anyway" is irresponsible, IMO. Anyone who truly understands the implications of doing something like this, will not be asking such a basic question. –Stephen

My opinion: Don't give them a gun if it's obvious they don't know how to handle one. They will end up shooting themselves or someone else in the leg.

share|improve this answer
    
I agree that I am not overly experienced that's why I am here ... :-) –  Radek Aug 16 '11 at 9:30
3  
Radeck: That is why most of us are here. Without questions the site doesn't exist. It's not meant to be disrespectful to you in any way, we just want to make sure you get good advice and not the dangerous kind. –  Wesley Murch Aug 16 '11 at 9:33
    
I don't feel any bad what was said here. I just cannot agree about not posting answers because it is not secure. One has his own reasons and others are judging form a position where they cannot see the whole picture. –  Radek Aug 16 '11 at 9:42
    
You are right that we cannot always see the whole picture, but one can often read between the lines and infer quite a bit from the way the question is presented. Besides there are things that you may not be aware of yourself. For instance, there are much better tools and methods for managing a database than a browser's address bar. –  Wesley Murch Aug 16 '11 at 9:52
3  
@Radek - We still have a principle here that when someone asks "How do I shoot myself in the foot?" we shouldn't say "You forgot the bullets.". Sometimes it is a judgement call how to read the question. –  Bo Persson Aug 16 '11 at 9:54
    
@Bo: I do understand your point buuuut you cannot apply it to my case. I cannot see any really any reason why not to do so. The only reason why I won't use it would be if the solution is not faster that the current one. –  Radek Aug 16 '11 at 10:01
    
@Bo: I am not going to harm any one, myself included. –  Radek Aug 16 '11 at 10:02
    
@Radek - Perhaps not hurting anyone now, but how about later? I know a bank that put account numbers in the question string. If you edited the string you got another person's account statement! Ouch!! –  Bo Persson Aug 16 '11 at 10:08
    
The code won't be used outside out company, it won't work. It is used right now by me so if do anything wrong the worst scenario is that I have to restore the db again. The other guys are more experienced so they won't do anything wrong if they do, they will have to restore the database again. –  Radek Aug 16 '11 at 10:14
1  
@Radeck: This discussion, while inspired by your post, is not limited to it. You are free to post your opinion as an answer here as well. –  Wesley Murch Aug 16 '11 at 10:35
    
@Bo that's interesting, what's that bank? evil :) –  J0HN Aug 16 '11 at 10:40
    
@J0HN - Not naming any names, but I know this was caught by an internal audit and caused "some changes" to the session handling. Basically you have to pass a random token instead and on return verify that the token belongs to the active session (which also stores the data server-side). –  Bo Persson Aug 16 '11 at 11:34
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .