Page 2 of 3

Posted: Thu Aug 24, 2006 6:41 am
by Ollie Saunders
in PDO it is yes, but not if you are interacting with the DB directly.

Posted: Thu Aug 24, 2006 6:47 am
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 (:

Posted: Thu Aug 24, 2006 6:48 am
by Ollie Saunders
:D

Posted: Thu Aug 24, 2006 6:55 am
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?

Posted: Thu Aug 24, 2006 7:00 am
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.

Posted: Fri Aug 25, 2006 3:50 am
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.

Posted: Fri Aug 25, 2006 5:04 am
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.

Posted: Fri Aug 25, 2006 5:35 am
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

Posted: Fri Aug 25, 2006 6:00 am
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.

Posted: Fri Aug 25, 2006 6:23 am
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);

?>

Posted: Fri Aug 25, 2006 7:08 am
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.

Posted: Fri Aug 25, 2006 8:23 am
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.

Posted: Fri Aug 25, 2006 10:26 pm
by RobertGonzalez
That is one handy little lesson. Man I love this community. Yet again, I am learning something cool.

Posted: Sat Aug 26, 2006 5:16 am
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)

Posted: Sat Aug 26, 2006 10:13 am
by AKA Panama Jack
You do know that your TillingDatabaseAccess.php is really a simple database abstraction layer. :D