Does Form Input That Goes In A Match Query Need To Be Sanitized?

[masterlayouts 3]

Should we sanitize it or the db will handle it?

 

$q  = 'SELECT subject, body FROM messages WHERE MATCH(body, subject) AGAINST('<IMG SRC="javascript:alert('XSS');">')';

 

Can you please put it in an working example?

[Larry 422]

I don't really understand what you're trying to do here, but first of all, you'll have a PHP parse error there.

[masterlayouts 3]

All form input has to be sanitized and run through mysqli_real_escape mechanism. My question is do we have to sanitize the form input for that/those field/fields that will add to a query whose function is just search and that input is never displayed or otherwise used in the application? As result risky user input as the attempt of XSS or SQL injection may happen. I am interested to know if the database will take care of the input and escape it or do whatever it is necessary by itself or do I have to sanitize the input before add it to the query?

 

$q = 'SELECT subject, body FROM messages WHERE MATCH(body, subject) AGAINST($_POST['unsanitizedUserInput'])';

 

user enter:

 

<IMG SRC="javascript:alert('XSS');">

 

Where user may enter anything at all, in my example I put for brevity an XSS attempt, that obviously cannot do anything wrong here, but it is not my example that I want to discuss but the concept behind it. I cannot think to any input that may cause problems, but some other people may do. Sorry for the syntax errors, I hope I am made myself clear even if the example chosen is wrong.

 

It is a good question as in the book the examples for fulltext index are very basic and do not discuss security issues at all. In all the other parts of the book you discuss them, as in the case of mysql statements where you said to run everything through mysql_real_escape() for example. I would like to know if in case of search we do have to run the input that goes only on search through such a function or not and in general what advise you can gave us security wise about FULLTEXT searches and user input.

 

In this example the query do not cause security problems, but maybe there are situations when it may cause security problems. Question remains: do we sanitize the user input that goes in searches or not? Thank you.

 

$q = 'SELECT subject, body FROM messages WHERE MATCH(body, subject) AGAINST(\'<IMG SRC="javascript:alert(\'XSS\');">\')';

[Larry 422]

All form input has to be sanitized and run through mysqli_real_escape mechanism.

 

That's your answer right there.

 

My question is do we have to sanitize the form input for that/those field/fields that will add to a query whose function is just search and that input is never displayed or otherwise used in the application? As result risky user input as the attempt of XSS or SQL injection may happen. I am interested to know if the database will take care of the input and escape it or do whatever it is necessary by itself or do I have to sanitize the input before add it to the query?

 

You're confusing two concepts here. With SQL injection, hackers submit bad data in the hopes of breaking the query and doing something bad. That's why you have to sanitize the user-supplied data used in a query. With XSS, code is stored and later displayed. Using a database-specific escaping function doesn’t prevent that. Instead you have to use strip_tags() or the like.

 

In any case, unless you're using prepared statements, the database will never automatically sanitize the input data. And, again, sanitizing the data doesn't prevent XSS.

 

It is a good question as in the book the examples for fulltext index are very basic and do not discuss security issues at all. In all the other parts of the book you discuss them, as in the case of mysql statements where you said to run everything through mysql_real_escape() for example. I would like to know if in case of search we do have to run the input that goes only on search through such a function or not and in general what advise you can gave us security wise about FULLTEXT searches and user input.

 

Fulltext index has nothing to do with this whatsoever. Run all external data through an escaping function before using it in a query, regardless of the query type.

 

In this example the query do not cause security problems, but maybe there are situations when it may cause security problems.

 

Not true. That query won't cause XSS problems but it's ripe for SQL injection attacks.

[masterlayouts 3]

Thank you for your answer. I guess we have one kind of problems with input and another kind of problems with the output.

 

I just gave an XSS example, but the question was meant for SQL injection as well. I am happy you address them both and made the distinction clear.

 

In my opinion, the first kind of problems does not necessary cause problems to the other and vice-versa. One can have a XSS problem if decide to output the result query and in that result figures the attacker's crafted XSS snippet, otherwise there is no problem. In this context I was asking about the queries that perform searches based on keywords of the type that occurs in fulltext, aka MATCH... AGAINST. I see no reason to disallow a search for an input that contains tags as far as I do not output the problematic input. In this sense, I guess, the form input does not have to be sanitized against XSS, but only against SQL injection.

 

Coming back to the injection issue I am pretty sure you know that this is not enough to prevent SQL injection. For example, the backtick it's not sanitized by this function. As result, all applications that use only the kind of protection you suggest are vulnerable.

 

To the best of my (limited) knowledge all form input should be reduced at minimum acceptable, like alphanumeric for most of the fields. For everything else that exceeds alphanumeric input, regular expressions are preferable catered precisely for that thing. For fields like address where no discernible pattern is possible I would pretty much stay with alphanumeric. For search queries, I believe the input field should be sanitized only against SQL injection and never used the output directly on the page. For quick and general use on small projects I guess the advise would be mysql_real_escape_string() + a regular expression that will search and eliminate all problematic characters such backtick (at least).

 

In my opinion, we should learn about mysql mode and programming without prepared statements only from an historic point of view. in fact PHP depreciated the mysql mode and suggest only mysqli and pdo.

 

Bottom line here is the mysql_real_escape_string() fail to sanitize the input in such a way that the injection to be prevented. After small research I find out that MySQL object names are not protected by encode_sql and this is the root of the problem. Also I find out it may be fixed at the server level in that particular file. There are a few interesting links I like to share:

 

http://stackoverflow...5811853#5811853

https://www.owasp.or...ion_Cheat_Sheet

https://makandracard...names-for-mysql

 

I hope this is useful. I am just surprised that a huge security hole such the failure of mysql_real_escape_string() to properly sanitize backtick was not mentioned in your book (or forum). It should be as unless prepared statements or stored procedure are used, everything else is vulnerable and the severity of the problem is critical. At the end of the day it's an easy fix: eliminate backtick's from user's input. But how many are actually doing it?

 

Overall, from the time you wrote the book I guess there are several thing that changed, such as everything you said about myisam and innodb engines. Now innodb engine supports fulltext indexing, so pretty much nobody will use myisam. Than everything else that lacks prepared statements should be just an historic perspective. It would be more interesting to be taught to write secure code from beginning. When it comes to mysql_real_escape_string() function it must be said always that it is not enough and at very list the input should be sanitized against backtick.

[Larry 422]

For example, the backtick it's not sanitized by this function.

 

So? What problem do you image that the backtick causes when used within a quoted string in a query?

 

To the best of my (limited) knowledge all form input should be reduced at minimum acceptable, like alphanumeric for most of the fields. For everything else that exceeds alphanumeric input, regular expressions are preferable catered precisely for that thing. For fields like address where no discernible pattern is possible I would pretty much stay with alphanumeric. For search queries, I believe the input field should be sanitized only against SQL injection and never used the output directly on the page. For quick and general use on small projects I guess the advise would be mysql_real_escape_string() + a regular expression that will search and eliminate all problematic characters such backtick (at least).

 

The most important thing is that you match the level of security to what's appropriate for the application. Also, why are you recommending that people use mysql_real_escape_string() when you yourself later say people shouldn't use the older, standard MySQL extension any more?

 

In my opinion, we should learn about mysql mode and programming without prepared statements only from an historic point of view. in fact PHP depreciated the mysql mode and suggest only mysqli and pdo.

 

True. Which is why I haven't used the MySQL extension in my books for years.

 

Bottom line here is the mysql_real_escape_string() fail to sanitize the input in such a way that the injection to be prevented.

 

I'm not sure that's true, but it's a different matter than whether mysqli_real_escape_string() does or does not have the same weaknesses you perceive mysql_real_escape_string() to have.

 

After small research I find out that MySQL object names are not protected by encode_sql and this is the root of the problem.

 

I think you're confused about what's going on here and in those examples. The mysqli_real_escape_string() function only protects data sent through it. If a program doesn't send a MySQL object name through it, then it's obviously not protected. But it'd be a terrible idea for a programmer to use a user-supplied MySQL object name in a query. In the rare situations where that is absolutely necessary, you should validate to appropriate object names, not try to sanctify the input regardless.

 

I am just surprised that a huge security hole such the failure of mysql_real_escape_string() to properly sanitize backtick was not mentioned in your book (or forum). It should be as unless prepared statements or stored procedure are used, everything else is vulnerable and the severity of the problem is critical. At the end of the day it's an easy fix: eliminate backtick's from user's input. But how many are actually doing it?

 

Again, I think you're confused/misguided as to what the real problems are. And it's quite naive to think that prepared statements and stored procedures aren't vulnerable to security holes. In fact, the common security hole in both involves something you already mentioned: MySQL objects.

 

At the end of the day, you'll come to the conclusions based upon what you believe, and do what you think is right, but, for the sake of my book and my forums, I have a different sense of things than you.

 

Overall, from the time you wrote the book I guess there are several thing that changed, such as everything you said about myisam and innodb engines.

 

Yes, with time, things do change. I wasn't aware that EVERYTHING I said about MyISAM and InnoDB has changed in the past 4 years. That seems like hyperbole. Everything. Really?

 

Now innodb engine supports fulltext indexing, so pretty much nobody will use myisam.

 

Yes, InnoDB now supports full text indexing. As of MySQL 5.6.4. The current version of MySQL: 5.5.24. So InnoDB now supports full text indexing, but by "now", I mean "will eventually". Also, this one little feature should not be the reason most people choose a storage engine, I expect.

 

It would be more interesting to be taught to write secure code from beginning. When it comes to mysql_real_escape_string() function it must be said always that it is not enough and at very list the input should be sanitized against backtick.

 

Again, I feel like I do a good job of teaching secure code from the beginning. You clearly seem to disagree. I still don't know why you continue to reference the older mysql_real_escape_string() function and I would love to know what you think the problem is with a backtick being present within a quoted string in a query.

[Larry 422]

By the way, I should add, that if I'm wrong about anything, I really want to be the first to know, as I never want to lead anyone astray. But from your post, it seems like you're grabbing random things you're reading elsewhere and drawing erroneous conclusions. And you admit to having "limited knowledge". I mean: this thread begins with you wondering if you have to sanctify user data used in a search form, where the answer should obviously be "Yes". But, again, if I'm wrong, I need to know that.

 

So, if you can provide specific, replicable, first-hand examples of security problems (e.g., how having a backtick that's not escaped in quoted user-provided data is a problem), and not just links to what you see other people write elsewhere, then please do.

[masterlayouts 3]

I am sorry, this being a written forum and not a speaking one you must took it the wrong way. Obviously I cannot pretend to you to be clairvoyant and anticipate changes in mysql engines. In fact you received similar questions about PHP6 and my opinion was (as you can probably check) that it cause no difficulties of understanding and learning PHP. I merely made a suggestion that the forum as well as the book should reflect major changes and I believe the new innodb with fullindex feature is such a major change. My point was not to through away the book, but to pin a thread, add a downloadable pdf annex to keep readers updated. I do not think is such a terrible idea and I do believe that people will continue the trend to drop myisam engine for innodb.

 

FULLTEXT searches require a FULLTEXT index, wich itself require a MyISAM table" (pag188).

 

One of the links posted is from owasp. They believe the first mechanism of defense against SQL injection is prepared statements. I would simply wanted to see more code in your book using prepared statements, this is what I understand by "more secure". Not that your code is unsecure, but if all reputable security sources are saying the same thing: use prepared statements, I guess a reason should be. Hence "more secure" code.

 

You were right, the tread started with the question posted in title: "Does Form Input That Goes In A Match Query Need To Be Sanitized?". Your answer was that all user input should be sanitized. But admittedly there are degrees here as well. If I do not use the output of the query why would I sanitize the input of the parameter that goes in MATCH... AGAINST? Advising me to sanitize all input do not carry too much weight, I would certainly expect a more detailed answer. My suggestion was to look at what goes to the database and what we output from the database as separate problems. I do not know if it is a good thought or not, I am not saying, I am asking. I was expected a debate, not a fight.

 

In any case, just for the sake of the argument, your redirect that you are using mysqli and not mysql (true), does not resolve the issue. mysqli_real_escape_string() does the same thing as mysql_real_escape_string(). What I said it fails to do is true for both of them:

"Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection. (...) Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z."

 

What I think failing to sanitize the backtick does is to create the risk of sql injection and it was rated as critical by owasp, not by me. I guess you understand better than me the examples offered. I can think to a simple fix, but maybe there are other solutions better than the one suggested. I guess all the examples offered in the links provided are replicable. I for one I checked if the backtick is escaped or not by the mysqli_real_escape_string function and it wasn't. Maybe I do not understand the depth of the problem, but it sure is a problem and I was hoping for your explanation and solution to the problem.

 

I always mention that I am a beginner. When I put something down I am eagerly waiting for your answer, sometimes I know it is something wrong, but I do not understand or not understand entirely why it is wrong, so I ask to find out. Otherwise I know what I am asking is simple, but I am asking to be sure. Rest assured that I was in no way disrespectful, it was not my intention. You can take a look at the ranking and review I gave your books. But I will be more careful in the future posts and create my own tag <suggestion>, <point of debate> and so on for not to be confused with other things as blame, disrespect, bad opinion...etc. If I am wrong everywhere else, you are wrong at least here: I do not think you are a bad teacher. au contraire.

[Larry 422]

I am sorry, this being a written forum and not a speaking one you must took it the wrong way.

 

 

Thanks for your explanation. Your message just had a strong tone of "You're doing things wrong and misleading people in the process." In fact, rereading it, your post still strongly sounds like that. And, really, this one does, too (e.g., the problem is that I took things the wrong way).

 

One of the links posted is from owasp. They believe the first mechanism of defense against SQL injection is prepared statements. I would simply wanted to see more code in your book using prepared statements, this is what I understand by "more secure". Not that your code is unsecure, but if all reputable security sources are saying the same thing: use prepared statements, I guess a reason should be. Hence "more secure" code.

 

As for the book, yes, I do update it. In fact, I just put out a new edition of it last year. And through these forums I can make recommendations and suggestions, which I do for free. Your specific suggestion that I get rid of mysqli_real_escape_string() and use prepared statements exclusively is not one I happen to agree with, though. If that's what you believe and want to do, than by all means do. But it doesn't mean that using mysqli_real_escape_string() is not secure. Or that you should ALWAYS use prepared statements. That's not what I believe, that's not what I've done in the book in the past, and it's not what I'm going to do in the book in the future until I have solid reason to think otherwise.

 

You were right, the tread started with the question posted in title: "Does Form Input That Goes In A Match Query Need To Be Sanitized?". Your answer was that all user input should be sanitized. But admittedly there are degrees here as well. If I do not use the output of the query why would I sanitize the input of the parameter that goes in MATCH... AGAINST? Advising me to sanitize all input do not carry too much weight, I would certainly expect a more detailed answer. My suggestion was to look at what goes to the database and what we output from the database as separate problems. I do not know if it is a good thought or not, I am not saying, I am asking. I was expected a debate, not a fight.

 

It's stunning to me that you are questioning the security approaches conveyed in my book but still aren't convinced that you have to sanitize all user input. No degrees here, you really have to. As I clearly explained above, sanitizing prevents SQL injection attacks, which is a danger whether the user's input will be shown on the page or not. This is a different issue than XSS. If you still don't understand what these things mean and how they differ, then reread the applicable sections of the book, search online, or use those other resources (that you seem to value more highly). I'm a person of very few absolutes, but YOU ABSOLUTELY MUST SANCTIFY ALL USER DATA USED IN QUERIES. This is the most basic security idea, truly. Honestly, I don't know how you can question whether mysqli_real_escape_string() is secure enough but still doubt that you have to sanctify user input used in queries, regardless of the query type. Go look up SQL injection attacks. Or, better, yet, try it for yourself using any type of a query and unsanctified user data.

 

 

What I think failing to sanitize the backtick does is to create the risk of sql injection and it was rated as critical by owasp, not by me. I guess you understand better than me the examples offered. I can think to a simple fix, but maybe there are other solutions better than the one suggested. I guess all the examples offered in the links provided are replicable. I for one I checked if the backtick is escaped or not by the mysqli_real_escape_string function and it wasn't. Maybe I do not understand the depth of the problem, but it sure is a problem and I was hoping for your explanation and solution to the problem.

 

No, mysqli_real_escape_string() does not sanctify the backtick, as far as I know. My question is: why is that a problem? Why are you "sure" that it's a problem? I can run a query with a backtick in it without any security problem whatsoever. In fact, I just did. No search online explains how having backticks in user-supplied data is a security problem. If you have a specific example of this, or a specific link you can show me, I'd love to see it. Otherwise my assumption is that this is a misunderstanding of the implication of backticks in queries. In the meantime, you seem to be blaming me for failing to explain something that I don't see as a problem, as if the universe as already agreed that it is a problem.

 

Rest assured that I was in no way disrespectful, it was not my intention. You can take a look at the ranking and review I gave your books. But I will be more careful in the future posts and create my own tag , and so on for not to be confused with other things as blame, disrespect, bad opinion...etc. If I am wrong everywhere else, you are wrong at least here: I do not think you are a bad teacher. au contraire.

 

I appreciate that you weren't trying to be disrespectful, although in this post it still does seem like you are still being quite disrespectful. But that's the way it's going to be sometimes. As I said, if I am wrong, I would love to be corrected, but being repeatedly told that I'm wrong when I really don't think I am, by someone who admittedly is unclear in some of the thinking involved, is a pretty annoying thing. No? I truly appreciate the positive reviews and comments on what I do, but your approach in asking questions is clearly rubbing me the wrong way.

[Larry 422]

I'm going to go ahead and close this thread, as I feel like it's no longer productive, nor can be. If you have any remaining technical questions that are unanswered, please post them as new threads.