"Hacking attempt" when inserting a complicated SQL query in one PHP article

[epfyffer]

Hello gurus,

So I ave a problem... I used a PHP article in my TinyPortal that displayed some infos based on the database.

This was done through a PHP article and worked fine.

The query in the PHP code was embeeded between (" ..... and "); and the world was perfect...

Here is the code that worked :

Code Select Expand



$mSql = ("SELECT
G.groupname,
M.memberName,
M.emailAddress,
M.birthdate,
M.websiteTitle,
M.location,
Max( case when P.id_profile =1 then PM.Choice end ) as Modele,
Max( case when P.id_profile =5 then PM.Choice end ) as FirstImmat,
Max( case when P.id_profile =6 then PM.Choice end ) as Couleur,
Max( case when P.id_profile =7 then PM.Choice end ) as Surnom,
Max( case when P.id_profile =8 then PM.Choice end ) as Km,
Max( case when P.id_profile =9 then PM.Choice end ) as Etat,
Max( case when P.id_profile =10 then PM.Choice end ) as Nom,
Max( case when P.id_profile =11 then PM.Choice end ) as Prenom,
Max( case when P.id_profile =12 then PM.Choice end ) as Datenaiss,
Max( case when P.id_profile =13 then PM.Choice end ) as Nationalite,
Max( case when P.id_profile =14 then PM.Choice end ) as Adresse,
Max( case when P.id_profile =15 then PM.Choice end ) as Ville,
Max( case when P.id_profile =16 then PM.Choice end ) as Pays,
Max( case when P.id_profile =17 then PM.Choice end ) as CP,
Max( case when P.id_profile =18 then PM.Choice end ) as Tel_Fixe,
Max( case when P.id_profile =19 then PM.Choice end ) as Tel_Portable
          FROM smf_membergroups as G, smf_members as M,smf_profile as P,smf_profile_choices as C,smf_profile_members as PM
          WHERE G.id_group = M.id_group and PM.id_member = M.id_member and PM.id_profile = P.id_profile and P.id_profile = C.id_profile and C.ID_CHOICE = 1 and M.id_group in (11,1)
          group by G.groupname,M.memberName,M.emailAddress,M.birthdate,M.websiteTitle,M.location");
   




Now, I just wanted to change my query with an other one, a little bit more complicated. Soo I just seleted all my old query, deleted it, and replaced it with my new one that work perfect in phpmyadmin.

So here is the new code, with the new query  :

Code Select Expand


$mSql = ("SELECT  Z.groupname,
Z.memberName,
Z.emailAddress,
Z.birthdate,
Z.websiteTitle,
Z.location,
Max( case when Z.id_profile =1 then Z.Choice end ) as Modele,
Max( case when Z.id_profile =5 then Z.Choice end ) as FirstImmat,
Max( case when Z.id_profile =6 then Z.Choice end ) as Couleur,
Max( case when Z.id_profile =7 then Z.Choice end ) as Surnom,
Max( case when Z.id_profile =8 then Z.Choice end ) as Km,
Max( case when Z.id_profile =9 then Z.Choice end ) as Etat,
Max( case when Z.id_profile =10 then Z.Choice end ) as Nom,
Max( case when Z.id_profile =11 then Z.Choice end ) as Prenom,
Max( case when Z.id_profile =12 then Z.Choice end ) as Datenaiss,
Max( case when Z.id_profile =13 then Z.Choice end ) as Nationalite,
Max( case when Z.id_profile =14 then Z.Choice end ) as Adresse,
Max( case when Z.id_profile =15 then Z.Choice end ) as Ville,
Max( case when Z.id_profile =16 then Z.Choice end ) as Pays,
Max( case when Z.id_profile =17 then Z.Choice end ) as CP,
Max( case when Z.id_profile =18 then Z.Choice end ) as Tel_Fixe,
Max( case when Z.id_profile =19 then Z.Choice end ) as Tel_Portable
FROM
(
SELECT C.*
FROM
(
SELECT B.*
FROM
(
SELECT A.*,G.groupname
FROM
(SELECT
D.*, PM.choice,PM.id_profile
FROM (
SELECT M.id_group,
M.memberName,
M.emailAddress,
M.birthdate,
M.websiteTitle,
M.location,
M.id_member
FROM smf_members M
WHERE M.id_group= 11)as D left join
smf_profile_members as PM on
PM.id_member = D.id_member
) as A
left join smf_membergroups as G
on G.id_group = A.id_group
) as B
left join smf_profile_choices as C
on B.id_profile = C.id_profile
) as C
left join smf_profile as P
on C.id_profile = P.id_profile
) as Z
group by Z.groupname,
Z.memberName,
Z.emailAddress,
Z.birthdate,
Z.websiteTitle,
Z.location");



But infortunatly this returns an "Hacking attempt" error...

Any idea or suggestion

Thanks in advance  :up: :up:

[JPDeni]

You'll have to come up with another way of doing your query that isn't as complicated, at least if you're going to use the db_query function, which I assume you are. It's all a security measure. You can see it if you open Subs.php and look for function db_query. You'll see all the "hacking attempt" things there.

I've run into it, too. There have been a number of times when I've written a query which worked fine when I tested in phpMyAdmin, but got a "Hacking attempt..." notice within SMF. Usually the only way to achieve the same result was to run several queries and then manually manipulate the results in order to come up with the data I wanted.

[bloc]

Also - try "mysql_query()" first. if it works, then you know its something unsafe about its variables etc,(which db_query reports) if not then its something wrong with the query itself.

[JPDeni]

I usually debug my queries within phpMyAdmin to see if my syntax is right and if I'm getting what I intended to get. Sometimes what I think I want isn't what I get at all.

[bloc]

Yes, picturing complex queries is sometimes bewildering, one has to think a certain way to get what you want really.

[feline]

On SMF you can't use SUB-Selects. SMF detect this and alert the Error.
Rewrite your query with joins ...

Fel

[epfyffer]

Hello,

So thanks a lot for your answers...

I will try to find someone able to write this based on join instead of sub select queries.


Thanks a lot for your assistance  :up: :up: