In MySQL can you use JOIN for INSERT queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: In MySQL can you use JOIN for INSERT queries?

Post by VladSun »

jaoudestudios wrote:...or use MySQLi with multiplie queries.
Warning: security issues (powerful SQL injections)!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: In MySQL can you use JOIN for INSERT queries?

Post by jaoudestudios »

Warning: security issues (powerful SQL injections)!
Really? I thought MySqli was MySql(improved) so better with speed and security?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: In MySQL can you use JOIN for INSERT queries?

Post by Eran »

multi_query allows you to execute many queries in one call as opposed to one. This opens more possibilities for SQL injection that wouldn't work with mysqli_query, so one needs to be more careful when using it.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: In MySQL can you use JOIN for INSERT queries?

Post by VladSun »

jaoudestudios wrote:
Warning: security issues (powerful SQL injections)!
Really? I thought MySqli was MySql(improved) so better with speed and security?
As pytrin said, I was referring to the "multiple queries" mode, not the mysqli library itself.

Let's have an inexperienced web developer (just like me ;)) who has written a vulnerable piece of code:

Code: Select all

$sql = 'select * from posts where post.author_id = '. $_GET['id'];
In a single query mode, an attacker is limited to read-only SQL injections like this:

Code: Select all

http://badsite.com/posts/view.php?id=1 or 1=1
In a multiple queries mode, an attacker may inject whatever he wants:

Code: Select all

http://badsite.com/posts/view.php?id=1;drop database user; drop database post;
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: In MySQL can you use JOIN for INSERT queries?

Post by VladSun »

Another approach for performing multiple queries is to use stored procedures.
http://dev.mysql.com/doc/refman/5.0/en/ ... tines.html

It will save bandwidth and increase performance and security. A side effect is that using stored procedures often moves parts of the business logic into the DB layer.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply