MySQL Forums
Forum List  »  Performance

Re: 2 small queries vs 1 big
Posted by: Roland Bouman
Date: August 28, 2005 01:58PM

Roman Ivanov wrote:
> Could you please explain why? Is it because of the
> query overhead?

Depend on what you mean by overhead. If we'd say, overhead is parsing, building execution plan, and allocating memory for the resultset, there's at least one more factor important for performance, the network traffic (Sending the request and receiving the result).
>
> #BTW what is the datatype of u.id and g.id? If its
> a numeric type you
> #shouldn't quote your value.
>
> I'm usually coding in PHP, so I always quote my
> values to prevent possible sql injection attacks.
> Does quoting numbers have some negative effect?

Could have. At the very least, an implicit conversion has to be done.

for data that's supposed to be integer, and i want to use it to generate a query string, i usually validate the data in php:

if (is_numeric($myint)){
$myint = intval($myint);
} else {
// deal with it.
}

or something like that. I think this is quite safe for integer data (Can anyone confirm or deny this? Does anyone have a better trick? Please let me know)

A generic way is to use the mysqli extension, and use parameterized prepared statements:

$mysqli = new mysqli(.....);

$query = '
SELECT u.id, u.groupId, u.name, u.info
FROM user AS u
WHERE u.id = ?
';

$stmt = $mysqli->prepare($query);
$stmt->bind_param('i',$myInt);

etc.

BTW, you should realise that quoting alone is not really a protection. Suppose someone sends a string like:

' or benchmark(999999999999999999,"select power(2,256)") or 1 or '

Without extra precaution, your query would now read:

SELECT u.id, u.groupId, u.name, u.info
FROM user AS u
WHERE u.id = '' or benchmark(999999999999999999,"select power(2,256)") or 1 or ''

So, instead of getting just one or zero rows, we are now fetching all the rows from the user table, and we are giving the server some extra work executing the benchmark

Now, this might not be really malicious, the point is, someone is actually injecting something.

Both the mysql and the mysqli extensions come with a function that helps a bit: mysql_real_escape_string and mysqli->real_escape_string. This escapes the string argument so that it will always remain a valid literal string constant.
So, if you really need to generate the values in the statement, and you want to prevent injection, I really recommed you use these. Quoting alone is not enough.

Options: ReplyQuote


Subject
Views
Written By
Posted
2344
August 27, 2005 12:46PM
1621
August 28, 2005 02:42AM
1435
August 28, 2005 12:34PM
Re: 2 small queries vs 1 big
1653
August 28, 2005 01:58PM
1410
August 30, 2005 08:15AM
1435
August 30, 2005 08:27AM
1497
August 30, 2005 05:50PM
1482
August 31, 2005 03:20AM
1573
September 07, 2005 11:56AM
1738
September 07, 2005 01:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.