Category: PHP

Properly escaping MySQL queries in PHP

I'm on various boards and such and from time to time people run into issues where they're trying to insert something into MySQL via a raw query and they inevitably run into that pesky apostrophe and the query dies.

Then almost always someone comes along to tell them that they need to use addslashes().

This is wrong.

Ideally you really want to use prepared statements (mysqli and PDO extensions), but let's assume for now you're throwing caution to the wind and you're going to do it the old fashioned way.

If you're using the mysql extention, you should use mysql_real_escape_string() around all of your variables which are not cast as integers. But actually, you shouldn't be using this function because mysql_* is deprecated, way deprecated. Instead you should be using…

mysqli which is faster, better, sexier, everything you want in a wom… extension. In this case we have the more logical name mysqli_escape_string() or you can use the back-to-goofiness-again method in the mysqli class $mysqli::real_escape_string() and it works the same way.

One issue is that with both of the above functions you have to actually be connected to the server to use them, that's because it escapes based on your connection chartype and some other stuff.

However assuming you're not too worried about potential unicode issues (I've yet to have any, supporting Serbian and Hungarian) you can always make your own function to escape based on what MySQL requires:

function escape($string) {
    return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"),
                       array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $string);
}

But there's always a potential danger in doing things yourself and I actually don't have proof the above is faster than the connection required escape functions, so just use prepared statements ideally.

So, what the hell is type casting anyway?

Casting is a way to take a liquid and mold it int… oh yeah

So casting is just a fancy way to refer to type conversion, that is where you change the "type" of a variable from one thing to another. For example changing a string to an integer.

How about some examples? Is that what you want?

OK, fine, you talked me into it. Here are some PHP examples:

$var = '1000';
var_dump($var); // Returns string(4) "1000"

$var = '1000';
$var = (int)$var; // Here is where we cast it
var_dump($var); // Returns int(1000)

So, who cares? What's the point?

Well, depending on what you're wanting to do, it's important to change the type, and this is especially true in languages where there is no dynamic typing (like C#) and it's still useful in languages with dynamic typing like PHP, because it allows for one to avoid potential issues with mathematics, concatenation, etc. Aside from math related things, in PHP I use (int) a lot to clean up variables for SQL queries for both safety and also so MySQL doesn't have to convert the types itself.

You can learn more about type casting in PHP specifically and why it's a great way to do certain things here: Casting int faster than intval in PHP.

Casting int faster than intval in PHP

For years I've been using intval($var) for being sure something is an integer, and sometimes using (int)$var to cast it into an integer, which essentially does the same thing — learn what casting is.

I'm here to tell you that unless you require the second parameter of intval(), which changes the base, then you should be casting instead.

In the case of casting int, it's about 300% or 3 times as fast as using intval(), and if you use it a lot like I do, for example in setting the correct type/checking for SQL queries, then it's time to switch. I know I am.

You can also pile them up if you want, for example:

$var = (int)(bool)$var;

This will change strings of "1" and "0", actual numbers of 1 and 0, boolean values, and null to True and False (Null is always False). Pretty useful if you've got a bit or tinyint(1) for pseudo-bool columns in your database or what have you, clean it up real nice.

It's sort of strange too because in other languages I always cast when available, but for some reason in PHP I got in the habit of using intval(), floatval(), etc.

Available casts:

$var = (array)$var;
$var = (b)$var;
$var = (binary)$var;
$var = (bool)$var;
$var = (boolean)$var; // Alternative
$var = (double)$var;
$var = (float)$var;
$var = (int)$var;
$var = (integer)$var; // Alternative
$var = (object)$var;
$var = (real)$var;
$var = (string)$var;

Let them learn COBOL / PHP isn't evil

I received this in my inbox earlier:

What programming languages should a modern-day programmer have in his/her arsenal? (Quora)

OK, fine, now I'm forced to evangelize for PHP, this puts me in a really painful position, but since I'm apparently the only person

reading this who can think for myself instead of freebasing whatever the Valley tells me to use, here we go…

The general theme seems to be to either learn a pretty hardcore language like C or C++ which won't benefit most people right away these days, since there's almost no excuse to make classic applications anymore. I think if anything it will discourage some people from learning to program since they have to spend a lot of time learning to clean up garbage, compiling, debugging, etc. Way to ruin their fun by making them spend all that time on a language better suited for drivers than web or phone apps.

Promoting Java is also a thing for some reason, I thought we were trying to kill this language? It's still used by a lot of places, but so is COBOL. In fact there's still a ton of places that use COBOL, so why not promote it? Probably because it doesn't come with a hipster mustache and a really tall bicycle.

If it's about job security, automatically Python and Ruby were a terrible suggestion, same with Erlang. You might as well be one of those skinny guys promoting Lisp.

A huge one though is promoting Python (and sometimes Ruby), blindly suggesting it's the best way to go without consideration for how huge of a pain in the ass it is to start a project. The syntax of the language(s) is very easy and the language itself quite powerful, but also slower than other options, harder to get going, and not widely supported. Starting a project in Python is about as difficult as starting a car by putting the engine in the car first. Turnkey? Hell no. You can get used to it, take some shortcuts, etc, but really for a new person, it's a nightmare.

It's really a hipster language, and Monty Python isn't funny, I'm just saying, it really isn't, seriously.

That's unrelated to this topic, but since Python is named after it, I felt it was important for me to communicate that it's just … knights who say Ni? yeah, falling over laughing. Monty Python films had a few snicker moments here and there, but it was mostly diarrhea (or diarrheoa). I liked Flying Circus much better, why don't many people talk about that?

Yes, I've seen all of the popular films, and no I didn't laugh. I didn't go into expecting it to be about as funny as a hernia operation either. I had thought they would be funny since that's what people were saying, and after wasting about six hours of my life I realized: holy crow, I didn't laugh once. No, I mean that literally, I didn't laugh one time. A few smiles, sure, but not much else.

Anyway, where was I? Oh yeah, terrible ideas…

Some other promotions for assembly, as if it's 1977 or something.

In general though there was a lot of PHP hate spread through the entire thread, mostly that it was bad, but nobody ever saying why, it just is. That's a lot of bullshit. It's because PHP is widely used, widely available, and despite their claims PHP has made a massive amount of headway over the last few years, and is only getting better.

Much of the complaints about PHP people have are true.. if you've fallen out of a time machine from 2004. Hating PHP is like hating MySQL, it's just easier to ignore the last decade and pretend nothing ever changes, then go on to promote your slower, less widely available, much cooler alternatives of Python and PostgreSQL.

It's just the toxic runoff coming from the Valley of essentially acting like Pookie for anything cool coming out of the Valley, Bay Area, etc. And hey, I've lived in the Bay Area, so that makes me an authority on everything there.

I don't mind the C# suggestion, I don't like the platform limitations. Yeah there's mono, but yeah, who cares. C# has a lot of things like static typing that I wish PHP had, but Hack from Facebook does add a lot of those features right back into PHP and many of those will be moved into core PHP over the next couple of years.

The blinding hatred of PHP out there causes people to promote things in a manner which can slow newcomers down. PHP sure isn't perfect and there are of things I'd change about PHP, but it's faster, extremely powerful, and most importantly easy as hell to get going.

I'm of the mind though if we're going to want to stop people from learning to program, then yes, let's promote Python, Ruby, Erlang (what the heck are you promoting this for, do people making small sites really need message queues? Don't be a jackass.), and while we're at it Java. Languages which can be easy at face value, easy in syntax, but a pain in the ass to get going and deal with, not to mention slower. Except Java and Erlang, those can be pretty fast.

So reasons not to learn PHP?

  1. It's not really cool
  2. It's not the steam punk of languages like Python, so you don't get a stupid ass top hat with non-functioning goggles and proclaim you're awesome
  3. It's making headway faster than most languages, some of which aren't even changing or improving at all any more.
  4. It's widely available, i.e. essentially everywhere, so you're not held hostage by host availability
  5. It will help you learn C-style syntax which you can more easily pass on to other languages like JavaScript (also used on the web), plus countless other languages like C, C++, Java, C#, etc

Python and Ruby aren't bad to have in your arsenal, but blindly suggesting them first, when C-style languages is king is just ridiculous. Meanwhile the most popular web language being PHP, which is a C-style language, oh no, don't use that, it's bad just because it's bad, I mean, no reasons listed here, it just is.

Anyway, now a choice, spend 10 seconds starting a PHP project or spend half an hour setting up a Python environment and prepping things just to get coding, and I mean really coding, throwing things directly to the interpreter isn't how you make real projects, it's how you demonstrate the language without making it obvious how much of a pain it is.

I'll use the language best suited for the situation, I'm not going to blindly dislike something because a broader community of self-deluded permanent man-children hate it.

My choices of languages:

  • PHP
  • JavaScript / node.js
  • Ruby
  • C#

My choice of languages in 2004:

  • Perl
  • C++
  • PHP

My choice of languages in 1997:

  • Perl
  • C++
  • Visual Basic

Nope, things never change, I'll just use Python forever and tell everyone that's all I've ever loved.

I hope you can appreciate the irony of blind hatred and ignorance of modern PHP meanwhile essentially doing the same thing with Python. That's my point, when it's turned around, it's obvious how idiotic you look.