Best practice - database mysql queries

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

in PDO it is yes, but not if you are interacting with the DB directly.
sike
Forum Commoner
Posts: 84
Joined: Wed Aug 02, 2006 8:33 am

Post by sike »

ole wrote:in PDO it is yes, but not if you are interacting with the DB directly.
just reviewed the code posted earlier in this thread and he uses mysql variables.
that won't protect you from anything - so you are right (:
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

:D
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

I am aware of sql injection. Read Shifletts and Ilia's books (may have to reread them).

I was specifically asking about the syntax used:

Code: Select all

SQL:
SELECT @message_id := $message_id;
SELECT message.body FROM message WHERE message.message_id = @message_id;
What's the @ and := for?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

In MySQL the @ sign is the equivalent of the $ sign in PHP - it declares a variable.

The := is the same as = in PHP - ie: it is used to assign a value to the variable.
User avatar
Case-
Forum Newbie
Posts: 22
Joined: Fri Aug 04, 2006 5:52 am
Location: Finland

Post by Case- »

ole wrote:I'd be interested to know if you think you could better it. Post back.
Dont think that I can better it, I just like to do it myself. Get to know whats going on under the hood better.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

sike wrote: just reviewed the code posted earlier in this thread and he uses mysql variables.
that won't protect you from anything - so you are right (:
Not quite true. What it does: it guaranties that in the second SELECT the value would be scalar. Sure, it may be still not the value you expect, because first SELECT is open to sql injection.
sike
Forum Commoner
Posts: 84
Joined: Wed Aug 02, 2006 8:33 am

Post by sike »

Weirdan wrote:
sike wrote: just reviewed the code posted earlier in this thread and he uses mysql variables.
that won't protect you from anything - so you are right (:
Not quite true. What it does: it guaranties that in the second SELECT the value would be scalar. Sure, it may be still not the value you expect, because first SELECT is open to sql injection.
that's the typical sql injection. doesn't matter if its the first or the second query that is vulnerable (at least for me).
if you don't sanitize the message_id i could easily inject this :

?message_id=1; DELETE * FROM users;

resulting in

SELECT @message_id := 1; DELETE * FROM users;
SELECT message.body FROM message WHERE message.message_id = $message_id;

cheers
Chris
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

in old version of sql you can send a null character which void the previous query allowing you to write a completely different one.
The defence in depth rule of security says that you should probably use SQL variables but keep escaping (with mysql_real_escape_string) as well.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

I've not actually tested it.. but can you run multiple queries like that and keep the value of the variables?

Code: Select all

<?php

$bar = 'something';

mysql_query('SELECT @foo := $bar', $link);
$result = mysql_query('SELECT * FROM `table` WHERE `column` = @foo', $link);

?>
sike
Forum Commoner
Posts: 84
Joined: Wed Aug 02, 2006 8:33 am

Post by sike »

Jenk wrote:I've not actually tested it.. but can you run multiple queries like that and keep the value of the variables?

Code: Select all

<?php

$bar = 'something';

mysql_query('SELECT @foo := $bar', $link);
$result = mysql_query('SELECT * FROM `table` WHERE `column` = @foo', $link);

?>
yes.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You can indeed. Which is obviously very useful. You can also do:

Code: Select all

insert into product_category (title) values ('category title');
select @category := LAST_INSERT_ID();
insert into product (title,category_id) values ('Product1',@category);
insert into product (title,category_id) values ('Product2',@category);
insert into product (title,category_id) values ('Product3',@category);
insert into product (title,category_id) values ('Product4',@category);
That allows you to create a SQL file that you can run in phpMyAdmin that automatically creates categories and products that are referenced to one another by id .. extremely handy for when you're populating a website.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That is one handy little lesson. Man I love this community. Yet again, I am learning something cool.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Two things people might want to know.
  • You can make multiple assignments in a single query.
  • Variables are case insensitive

Code: Select all

mysql> SELECT @foo := 10, @bar := 20;
+------------+------------+
| @foo := 10 | @bar := 20 |
+------------+------------+
|         10 |         20 |
+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT @foO;
+------+
| @foO |
+------+
| 10   |
+------+
1 row in set (0.00 sec)
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

You do know that your TillingDatabaseAccess.php is really a simple database abstraction layer. :D
Post Reply