MySQL correct SELECT

[saljutin]

well for some long time I dont know what is correct and what is WRONG, so which version to use:

1.) mysql_query("SELECT * FROM `tabela` WHERE `number`='5' AND `text`='txt' LIMIT 1");
2.) mysql_query("SELECT * FROM tabela WHERE number=5 AND `text`='txt' LIMIT 1");
3.) mysql_query("SELECT * FROM tabela WHERE number=5 AND text=txt LIMIT 1");

I was looking all over internet about information when to use `` and '' and WHY is it wrong if you use them and you shouldn't.
for '' people say DON'T use it if you have numerical entry - but WHY?
for `` I haven't found anything why to use or why not

When I began my PHP coding I didn't use ` nor ', but then some query didn't work and someone offered me potentional solution to add ` and ' to that SELECT sentence. That appeared to work so from that time every query I do ... I use ` and '
So what is wrong and why?

[JGadrow]

Using backticks (`) for your identifiers is only required if that identifier clashes with a MySQL reserved word. Adding the backticks doesn't really harm anything other than adding a slight processing overhead that could have been avoided otherwise. But, it's so small an improvement as to not really be an issue.

As far as using single quotes (') for values, this tells MySQL that the value is a string type. It takes longer to process a string than it does an integer which is why it shouldn't be used for integer values. However, all string values need to be enclosed in quotes or MySQL believes that you're trying to use an identifier instead.

So, for the examples you provided, #3 would not function as intended and #2 would be slightly more optimal than #1. The optimal query here would be:

Code: (sql) [Select]

SELECT * from tablea WHERE number=5 && text='txt' LIMIT 1;
Notice that I utilized the ampersands in place of "AND". This reduces the query by a single character which optimizes it that much further. I'm assuming that neither number or text are a primary field in this table as you have specified the limit clause which would be implicit if either value was primary.

Hopefully that helps.

[Chris]

I use that one:

Code: [Select]

mysql_query("SELECT * FROM tabela WHERE number=5 AND text='txt' LIMIT 1");

Code: [Select]

for '' people say DON'T use it if you have numerical entry - but WHY? Because it is a bit slower (conversion from string to int required). BUT, sometimes it is good to make num='$num' if you haven't made int conversion/check before to avoid SQL injection.

Generally, use '' for strings and user entered data if you do not force type conversion.

Code: [Select]

Notice that I utilized the ampersands in place of "AND". This reduces the query by a single character which optimizes it that much further. Nice tip If I hadn't had my habits already I would switch to &&/||.

[JGadrow]

Nice tip If I hadn't had my habits already I would switch to &&/||.

Yeah, I'm already in the habit of doing this for program code anyways so I never got into the "natural language" syntax. lol

[dsheroh]

Because it is a bit slower (conversion from string to int required). BUT, sometimes it is good to make num='$num' if you haven't made int conversion/check before to avoid SQL injection.

But, then, you should always be using placeholders to avoid SQL injection anyhow, or doing appropriate conversions/checks if you're stuck with a database interface which doesn't support them.  Placeholders also nicely sidestep the quoting issue, as you don't have to quote values provided via placeholder.

Code: [Select]

my @res = $dbh->selectrow_array('SELECT * FROM tabela WHERE number=? AND text=? LIMIT ?', undef, 5, 'txt', 1);
or

Code: [Select]

my $sth = $dbh->prepare('SELECT * FROM tabela WHERE number=? AND text=? LIMIT ?');
my @res = $sth->fetchrow_array(5, 'txt', 1);
(Note: Perl code, not PHP.)

[saljutin]

@JGadrow - thank you, nice post, all clear now, +REP

as far as about SQL injection and/or XSS, I use addslashes() for every string entry and round() for every numerical entry, so I guess I escape potentional SQL injection and XSS attack

[dsheroh]

as far as about SQL injection and/or XSS, I use addslashes() for every string entry and round() for every numerical entry, so I guess I escape potentional SQL injection and XSS attack

As you may have guessed, I'm not a PHP expert, but my understanding is that, if you're not going to use placeholders, you should be using mysql_real_escape_string(), not addslashes().  A bit of googling turned up http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string which shows an SQL injection scenario which it claims can slip through addslashes(), but is stopped by mysql_real_escape_string().

Either way, although string escaping can provide excellent protection against SQL injection, it cannot provide absolute protection.  Placeholders/parametrized queries can and do - they send the query and the data values to the database engine separately, so there is no way that it can be tricked into interpreting a data value as a command.  (They can also provide a nice performance benefit on frequently-run queries if you're able to store a reference to the prepared query and just re-execute it with different parameters as needed instead of forcing the database engine to analyze and prepare the query each time.)

[JGadrow]

dsheroh is correct in that placeholders (otherwise known as prepared statements) are the best form of protection against SQL injection. However, depending upon how your system is implemented it may not be strictly necessary and may hurt performance. As with anything in programming, the actual implementation trumps everything. In PHP, you cannot utilize prepared statements with MySQL unless you're using the MySQLi extension (as opposed to the MySQL extension) and you must be running at least MySQL Community Server 5.0.7 or 4.1.13. Unless you haven't updated in quite a while, that shouldn't be a problem.

Also, it is correct that if you are unable to utilize prepared statements and it's critical that you sanitize your data (no sanitization is strictly necessary if you're able to perform validation prior to insert / update), that you should use the proper escape function provided by the database connector as it will be the one that is best able to determine what is most harmful for that implementation.

XSS is another beast entirely and doesn't really bear mentioning in this case.

[Chris]

Me staying in a corner with a big transparent "No to sql placeholders!"

[dsheroh]

Me staying in a corner with a big transparent "No to sql placeholders!"

Why's that?  Just a matter of portability (in the PHP world, at least) or is there some other aspect you don't like about them?

[Chris]

Me staying in a corner with a big transparent "No to sql placeholders!"

Why's that?  Just a matter of portability (in the PHP world, at least) or is there some other aspect you don't like about them?

Performance.

[-fedexer-]

Me staying in a corner with a big transparent "No to sql placeholders!"

Why's that?  Just a matter of portability (in the PHP world, at least) or is there some other aspect you don't like about them?

Performance.

Out of curiosity, what makes them so bad performance wise?

[JGadrow]

Out of curiosity, what makes them so bad performance wise?

The way they work is that you first tell the server the general format that you're going to be sending queries in. MySQL then says, "Ok, I'm not going to do ANYTHING other than what you told me you're going to do." This is why it's great protection against SQL injection.

Then, you tell it, "Here's the missing puzzle pieces for that query." And it then returns you the results based upon that input.

So, if you're only executing a single query with prepared statements, you're doubling the number of queries that are necessary to achieve the result.

However, if you're running a batch of identical queries with different data, prepared statements actually offer a performance benefit because the server knows what you're doing in advance and doesn't need to translate the query each time, just plug the input in and give results.

[Chris]

Me staying in a corner with a big transparent "No to sql placeholders!"

Why's that?  Just a matter of portability (in the PHP world, at least) or is there some other aspect you don't like about them?

Performance.

Out of curiosity, what makes them so bad performance wise?

I don't know. There were some stats posted on the forum with comparison, even Lite version was many % slower than the raw implementation (I do not remember the exact numbers).

[aerosuidae]

As people have mentioned, quoting a number literal as a string requires MySQL to perform a cast operation before using it, which is a little extra overhead.  Some additional gotchas:

1. If you compare a string column to a number literal (ie, WHERE varchar=5), then MySQl cannot use any index that may exist on the string column. The same rule applies if comparing a string column to a number column (ie, WHERE varchar=integer) with no literal involved. So, if you decide to be speedy and write your queries to meticulously avoid quoting all numbers, just step back and make sure you don't do it in this case because you'll get painful table scans happening.

2. Whenever two values with different data types are compared (string=number, essentially) MySQL converts both values to floating point (real) numbers first. Floating point comparison is a little slower than integer comparison which can hurt on large datasets.  It's also inexact, if that matters to your app (probably no big deal for games, but definitely a consideration in business apps or anything dealing with real money).

3. Bugs relating to MySQL automatic type conversion are not unknown.  More could be found.

Simply, be safe as well as fast: ensure every comparison is between values with the same data type, or if not, ensure you know the reason why!

http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

[Chris]

1. If you compare a string column to a number literal (ie, WHERE varchar=5), then MySQl cannot use any index that may exist on the string column. The same rule applies if comparing a string column to a number column (ie, WHERE varchar=integer) with no literal involved. So, if you decide to be speedy and write your queries to meticulously avoid quoting all numbers, just step back and make sure you don't do it in this case because you'll get painful table scans happening.

You mean "... WHERE id='5' " will not use the id field index?

2. Whenever two values with different data types are compared (string=number, essentially) MySQL converts both values to floating point (real) numbers first. Floating point comparison is a little slower than integer comparison which can hurt on large datasets.  It's also inexact, if that matters to your app (probably no big deal for games, but definitely a consideration in business apps or anything dealing with real money).

Are integers (8 and 16 bit comparison, like TINYINT and SMALLINT) converted to floats too?

What about signed int and unsigned int comparison?

[aerosuidae]

You mean "... WHERE id='5' " will not use the id field index?

No   Sorry if I was unclear.  I was referring to string_column = unquoted_number.

Assuming your `id` is an INT field, WHERE id='5' will use the index on `id`. This is because MySQL can accurately cast a string to a number, so it does so for you and then uses any index on `id`.

However, if your `id` is a string field (CHAR, VARCHAR, etc), then WHERE id=5 (that is an unquoted 5) could not use the index. This is because MySQL cannot accurately convert the number to string in this case, so it simply does not try.  For example, the number 5 could be represented as many different strings, like '5' or '5.00' or '0x5'... which string should MySQL match in an index?  And what about leading or trailing whitespace, etc...

I admit this case of string_column = unquoted_number is not a big factor in most well designed applications, because in the process of designing and normalizing a schema properly you tend to automatically avoid this sort of situation.  I only meant it as an example of the dangers of blindly assuming that numbers should never ever be quoted in queries, and to offer some insite into MySQL.

The common place I've seen this bug is with query generator code doing something like:

Code: [Select]

$sql .= ' WHERE ' . mysql_real_escape_string($field) . ' = ' . (is_numeric($value) ? $value : '"' . mysql_real_escape_string($value) .'"');
Yep, it's nice and fast for most situations when $field is a numeric data type.  But it can become a fun performance bug to find when $field is a string data type and some user supplies a number as $value.

(and no, I don't condone building queries like this!  untested pseudocode example, etc. )

What about signed int and unsigned int comparison?

No worries there.  Integers of any size or sign can be compared and an index can be used.

[dsheroh]

Floating point comparison is a little slower than integer comparison which can hurt on large datasets.  It's also inexact, if that matters to your app (probably no big deal for games, but definitely a consideration in business apps or anything dealing with real money).

Am I correct to assume that this inexactness is solely an issue in case where the limitations of the underlying IEEE floating-point notation manifest themselves (in which case the same problems would appear with floating-point data types anyhow)?  Or is MySQL doing something in its casting process which introduces additional sources of potential errors?

[Chris]

What about signed int and unsigned int comparison?

No worries there.  Integers of any size or sign can be compared and an index can be used.

How exactly is it done? There must be some casting done, right?

8 bit #FF signed and #FF unsigned are different values that should be not equal. How this would be interpreted? Without conversion there would be a bug. So it must be converted to +1bit datatype for purposes of conversion, or the sign bit would be lost. So, intuitively, 8bit signed & 8bit unsigned comparison would require them to be converted to 16bit values first, while 8bit samesign types could be compared directly. Unless there is some x86 CPU command to make some magic here (my adventure with ASM was a short one and in ancient times so I'm far outdated when it comes to modern chipsets and could overlook something)...

If that's correct then anything other than identical datatypes comparison would be slower (not much of course, since we are talking about CPU command level, but still if the storage engine needs to make this conversion for all indexes on the fly...)

[aerosuidae]

Am I correct to assume that this inexactness is solely an issue in case where the limitations of the underlying IEEE floating-point notation manifest themselves (in which case the same problems would appear with floating-point data types anyhow)?

You're correct on both counts.  This is why MySQL offers the DECIMAL data type too. It is more accurate than FLOAT and DOUBLE.

http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html

How exactly is it done? There must be some casting done, right?

Yep, you have the right idea.  Essentially you just pad out both values with 0 so they have same number of bits, then compare them.  Signed-ness is just an extra or a dedicated bit on many platforms, which can usually be compared in the same operation.  There are indeed some cool ASM operations on many modern processors to do the whole process atomically (ie, with zero overhead).  Some older chips you had to go a bit crazy with loops and boolean logic to do the padding step, but those aren't the chips MySQL generally deals with