Page 2 of 2

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

Posted: Sun Dec 14, 2008 7:11 am
by VladSun
jaoudestudios wrote:...or use MySQLi with multiplie queries.
Warning: security issues (powerful SQL injections)!

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

Posted: Sun Dec 14, 2008 10:05 am
by jaoudestudios
Warning: security issues (powerful SQL injections)!
Really? I thought MySqli was MySql(improved) so better with speed and security?

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

Posted: Sun Dec 14, 2008 10:07 am
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.

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

Posted: Sun Dec 14, 2008 1:17 pm
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;

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

Posted: Mon Dec 15, 2008 1:47 am
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.