Comment On SQL MUGging

It also fails to account for the offending text being the first character of the variable.

I could have been frist before, but choose not to pollute the boards

Okay, for the non-PHP, non-SQL people among us... could someone please explain what's going on here?

I always worry that my code will one day grace these pages, but after reading stories like these, I breathe a little sigh of relief.

Why reinvent the wheel, even with RegExps? By using prepared statements you get injection prevention for free...

"Every child knows this is insecure and that Best Practice would be to harness the power of regular expressions"

I wwas told that Best Practice is the use of prepared statements but I wouldn't be such a prick to say "every child knows".

Some child:

I was told that Best Practice is the use of prepared statements but I wouldn't be such a prick to say "every child knows".

+1 :)

What they're doing is checking the username and password for special characters that can be used to escape a SQL statement and inject their own code into the statement.

Some child:

"Every child knows this is insecure and that Best Practice would be to harness the power of regular expressions"

I wwas told that Best Practice is the use of prepared statements but I wouldn't be such a prick to say "every child knows".

you don't even need to use prepared statements; you can just write a function called QuoteStringForDatabase and use this instead of putting quote characters round the value

If only there were some kind of

mysql_real_escape_string

function.

JD:

What they're doing is checking the username and password for special characters that can be used to escape a SQL statement and inject their own code into the statement.

What they've done is completely ineffective, but ok.

Then "every child" must be a moron, because that solution is even worse than the original code... FAIL!

Some child:

"Every child knows this is insecure and that Best Practice would be to harness the power of regular expressions"

I wwas told that Best Practice is the use of prepared statements but I wouldn't be such a prick to say "every child knows".

"Every child" does indeed know this gobble about regular expressions. Those of us who have grown up and learned a few things know better, and we use baseball bats^W^Wprepared statements.

TheSHEEEP:

Okay, for the non-PHP, non-SQL people among us... could someone please explain what's going on here?

Now, hopefully this can be transmitted without sounding like a troll (ahem), but I do have an honest question about this question:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying? I guess I'm asking coming from the position that there aren't many languages (at least within a familiar paradigm) that can just *be read* by anyone who understands programming. I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

CAPTCHA: venio
"venio, vedio, viccio" -> "I come because of the vice Vedius had."
(Damn that Vedius.)

Hello, welcome to no edits.

> "that can just *be read*" -> "that can't just *be read*"
> "venio, vedio, viccio" -> "venio, vedio, vicio"

CAPTCHA: similis
"I don't like Vera's similis 'tude one bit."

Sure use Regex, much better. Hope that was meant as Joke... Also mysql_real_escape_string is not safe at all by itself.

What I like is that the first case tests for " ", completely negating the last half dozen.

Guest:

mysql_real_escape_string is not safe at all by itself.

Yes, but what part of PHP is safe by itself?

Sea Sharp, Waves Hurt:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying?

My guess is that they're saying that they're not familiar with <language> and would like to keep it that way.

Mario Vilas:

Then "every child" must be a moron, because that solution is even worse than the original code... FAIL!

Whoosh!!

"Every child knows" that the Internet is killing the art of sarcasm.

dgvid:

"Every child knows" that the Internet is killing the art of sarcasm.

Oops. I forgot to include a meme-injection attack in my comment. How's this?

My every child was killed by knowledge and I assure it was no laughing matter.

Steve The Cynic:

Those of us who have grown up and learned a few things know better, and we use baseball bats^W^Wprepared statements.

Which HTTP return code hits the user with a baseball bat? This is relevant to my interests.

And I'm going with "Every child knows..." as being a joke. Because I just couldn't handle it not being a joke.

TheSHEEEP:

Okay, for the non-PHP, non-SQL people among us... could someone please explain what's going on here?

A fair question. If you build your SQL query using strings the user provides, someone will be either clever enough or stupid enough to break your SQL, sometimes intentionally and sometimes in ways that compromise your data/users/site whatever.

Programmers who don't know better think they can sanitize the inputs and thus create safe SQL from it. They may reduce the window of vulnerability, but there is a better way.

The better way is "prepared statements." So instead of building SQL code to execute directly like this:

$sql = "select muguser_id, muguser_directory " . 

       "from mugusers " . 

       "where muguser_active = 1 " . 

       " and muguser_email = '" . $_POST["email"] . "' ";

you would make a prepared statement:

$stmt = $dbh->prepare("select muguser_id, muguser_directory " . 

       "from mugusers " . 

       "where muguser_active = 1 " . 

       " and muguser_email = ?");

if ($stmt->execute(array($_POST['email']))) {

  while ($row = $stmt->fetch()) {

    # do something wonderful;

  }

}

This ensures the SQL statements are known text; they aren't built from any bits supplied by user input. Likewise, your user's input is not polluted by strange quoting and string interpolations from your host language (in this case, php).

This same technique is available in pretty much any language that can make SQL calls.

And now you have no excuse.

I am waiting to see some guy bring out Boby Tables

Todd Lewis:

And now you have no excuse.

+1

I see - this is the code you use to keep track of who still hasn't been sent their WTF mug? Good of you to share.

I had a mug once. It was funny.

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying? I guess I'm asking coming from the position that there aren't many languages (at least within a familiar paradigm) that can just *be read* by anyone who understands programming. I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

Unless that language is perl.

PedanticCurmudgeon:

My guess is that they're saying that they're not familiar with <language> and would like to keep it that way.

I guess I can think of a few I'd put on that list.

Kyles:

Unless that language is perl.

Perl is its own paradigm. More than that, it's its own philosophical system. I can definitely say that I can't understand some Perl I've seen.

Clever MUGgles, they don't know how to use the magic of mysql_real_escape_string, so they have to resort to ingenious workarounds.

Sea Sharp, Waves Hurt:

Perl is its own paradigm. More than that, it's its own philosophical system. I can definitely say that I can't understand some Perl I've written.

FTFY

Once again, TRWTF is toys like PHP and MySQL for not having bound parameters and prepared statements from the start, and for people using toys like that in production. Not like mysqli, which finally does support prepared statements, hasn't been around for an entire major version of both PHP and MySQL...

Oh, a photo site, maybe?

Seriously? "Every child knows regular expressions are the answer"...and people don't catch the biting sarcasm?

Captcha: "suscipere". Yes, I acknowledge the sarcasm.

Kyles:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying? I guess I'm asking coming from the position that there aren't many languages (at least within a familiar paradigm) that can just *be read* by anyone who understands programming. I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

Unless that language is perl.

Or JCL.

If one considers JCL An language, and not some horrible misguided practical joke taken to extremes.

Sea Sharp, Waves Hurt:

Perl is its own paradigm. More than that, it's its own philosophical system. I can definitely say that I can't understand some Perl I've seen.

Perl is not it's own paradigm. It is in the same family of languages as BrainF*ck, Taxi, and Piet. Someone just forgot to tell the Perl users it was a joke.

From a usability standpoint, it is nearly to the level of LOLCODE.

Yes Akismet, I just learned to [ab]use url tags. No Akismet, this is not spam.

QJo:

I had a mug once. It was funny.

I have a mug now. It is boring.

I think the learning curve is on a %2 years:

0-2: no protection
2-4: @see OP
4-6: addslashes()
6-8: mysql_escape_string()
8-10: mysql_real_escape_string()
10+: Become a manager and forget all the above.

What do they have against SPACES? Surely there's no way you can use a space character to cause a SQL injection...

Why would you not use a prepared statement ?
Where are these people coming from who don't yet know about prepared statements ?

Sea Sharp, Waves Hurt:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying? I guess I'm asking coming from the position that there aren't many languages (at least within a familiar paradigm) that can just *be read* by anyone who understands programming. I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

From what I gather, a lot of these questions arise because the issue with the code at-hand is some subtle nuance of the specific language or how the language interacts with something not language specific (e.g., a database).

Sea Sharp, Waves Hurt:

TheSHEEEP:

Okay, for the non-PHP, non-SQL people among us... could someone please explain what's going on here?

Now, hopefully this can be transmitted without sounding like a troll (ahem), but I do have an honest question about this question:

I have an honest question about you questioning the original question: what gave you the idea that everybody here has a programming or technical background?

Sea Sharp, Waves Hurt:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying?

Um... When someone asks you to pass the sugar, what do you think they're asking?

Sea Sharp, Waves Hurt:

I guess I'm asking coming from the position that I can't think beyond myself and am trying to sound smart while doing so.

FTFY

Sea Sharp, Waves Hurt:

I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

Congrats, that's 250 lines of PHP more than I and a huge majority of people have ever done. What makes you think that some anecdotal observation isolated to yourself only applies to the whole?

I don't mean to sound like a troll, but I hate these smug "You're a moran for asking" comments.

Paul Neumann:

Perl is not it's own paradigm. It is in the same family of languages as BrainF*ck, Taxi, and Piet.

Well, yes. Not literally :). Given how ridiculous it is, sometimes it seems to be. Forgive my lack of markup on the metaphor.

neener neener:

From what I gather, a lot of these questions arise because the issue with the code at-hand is some subtle nuance of the specific language or how the language interacts with something not language specific (e.g., a database).

I suppose this time it seemed more odd given the code at hand.

--

Also, I read this title as "SQL Munging". I guess that's not entirely innacurate.

I think something about this site is making me unable to spell properly... (I swear I'm proofreading before I hit Submit.)

> "innacurate" -> "inaccurate"

Bert:

Guest:

mysql_real_escape_string is not safe at all by itself.

Yes, but what part of PHP is safe by itself?

Any part of PHP is safe by itself. It's when you hook it up to something else, like the Internet, that you're doomed.

Seriously, prepared statements are so easy, and everyone keeps trying to remind you that they are the right way to do it, so why would anybody say "yeah but why can't I keep trying to build a better black-list filter?"

Use prepared statements.

Or stop programming.

Those are your choices.

C-Octothorpe:

I have an honest question about you questioning the original question: what gave you the idea that everybody here has a programming or technical background?

I'm going out on a limb here, but the fact that this was posted in the CodeSOD catagory might be a hint? You know, Code Snippet of the Day? Might be directed at people who (know) code? Far fetched, I know..

Really though, complaining that an article in CodeSOD is not understandable for non-coders is a bit like going to a star-trek forum and asking who this Picard fellow is. Anyone with basic programming skills should know that blacklisting is not a reliable method to prevent SQL injection, and this code is a prime example of why we have so many SQL injection hacks.

y u no
mysql_real_scape_string($email_or_pwd_or_un)
?!

Guest:

Sure use Regex, much better. Hope that was meant as Joke... Also mysql_real_escape_string is not safe at all by itself.

New here? A favorite pastime at the DWTF is to find a more elegant way to express the original WTF without fixing the root problem. Such as using a regex in place of a long if/elseif/elseif/elseif... mess - but without fixing the root problem. It's one of the reasons I like this site :-)

Captcha: appellatio. An obscene act performed on an apple.

Kyles:

When someone says, on here, that they aren't familiar with <language> and would like someone who is to explain, what are they actually saying? I guess I'm asking coming from the position that there aren't many languages (at least within a familiar paradigm) that can just *be read* by anyone who understands programming. I have done maybe 250 lines of PHP in my life and I can grok what's going on here pretty completely.

Unless that language is perl.

Or APL.