multiple statments in mysql

[yfatgo]

Hello,
I'm using php version 4.0 with mysql 5.0.24
connecting to the db using mysql_connect and running the statments using mysql_query.
I want to know how to restrict the queries so it won't allow sending multiple statments.
(I know that if I put '\' after the end of the first statment the second one will be ignored but I cannot convert any ; to ;\ since I put data from the user in the query)

Thanks

 

[Miros]

Are you saying you're letting people type SQL commands on a webpage and just assuming they aren't somehow hazardous to your system?

If you must let people do this and only want one command at a time, you can replace the ; with ;\ in the user input, or even lop off everything after the ;. At that point, it's your variable, do what you want with it.

I'd feel much safer if they were typing just a few parameters (like field1=3) and my script controlled the table and other big details.

 

[yfatgo]

lets say I have a command of "insert into tableX values('a',b)"
a = 'a;'
b = '1); delete from tableX where a in (1,2,3'
as a,b are imput from a user

I'll get:
insert into tableX values('a;',1); delete from tableX where a in (1,2,3)

I don't want to replace ; with ;\ cause the command will not work, and if not then 2 commands will be running

How can I avoid it ?

 

[KarveR]

see the manual for mysql_real_escape_string


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

insert into tableX values('a;',1); delete from tableX where a in (1,2,3)

does this work in php anyway? i thought mysql_query() would only execute one query at a time.

 

[KarveR]

mysql_query submits a string, regardless of what it is - hence the requirement of mysql_real_escape_string, to prevent injection attacks.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

are you sure? for example, for me (win32, php 5.1, mysql 5)) :

<?
mysql_connect ("localhost", "", "");
mysql_select_db("testdb");
mysql_query(
 "insert into test values ('a', 'b'); insert into test values 
('c', 'd');");
echo mysql_error();
?>

i get an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; insert into test values ('c', 'd')' at line 1

also mysql_real_escape_string would not stop the OP's issue. it just escapes the special characters according to the charset of the active mysql connection (sfaiU)

as said above, i can't find any evidence in the manual of this (other than user contributed notes) but i have always understood that mysql_query will only execute one query: it's more intelligent than a string pusher.

 

[Miros]

Personally, I'd feel LOTS safer with this type of set up:

Form with 2 fields, A and B.
Script that takes those two field values, A="C" and B="D", for example, and builds the query like this:

$query = "insert into tableX values('" + $_POST['A'] + "','" + $_POST['B'] + "');";

$query should then be equal to:

insert into tableX values('C','D');

The delete would be a separate form and script.

Letting the user type the whole query scares the poop out of me! They could delete your whole table, create new ones, gah, I need to stop thinking about this! It's like giving the whole world access to phpadmin!

 

[KarveR]

jpadie, I wouldn't feel it appropriate to explain on this forum just how to execute an injection attack, but would stress that mysql_real_escape_string does indeed serve a very valid purpose.

If you trawl enough dubios sites, you'll get the idea.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Miros]

I think we can agree, you gotta have a screw loose to let users type the whole query string. Gah!

 

[KarveR]

You can request data only, talented hackers can and will circumvent your code if you leave glaring holes in it.

Even addslashes() can be fooled.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.