Best practice - database mysql queries
Moderator: General Moderators
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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:
What's the @ and := for?
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;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 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 (:
that's the typical sql injection. doesn't matter if its the first or the second query that is vulnerable (at least for me).Weirdan wrote: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 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 (:
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
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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.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); ?>
You can indeed. Which is obviously very useful. You can also do:
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.
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);
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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)- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm