[SOLVED] Mysql_escape with an array

[Lilly]

Let me start by saying that I am totally new to PHP and MySQL. I am just learning - and the code I was given/am currently using is WAY beyond my current level of knowledge; so please be understanding if I say something totally stupid 

 

So, I am trying to get the mysql_escape_string function to work with an array. I found a useful tutorial online that suggested the following:

 

      if(!function_exists(mysql_real_escape_array))

                {function mysql_real_escape_array($t)

                        {return array_map("mysql_real_escape_string",$t);

                        }}

        mysql_real_escape_array($_GET);

        mysql_real_escape_array($_POST);

 

So, I would like to try out this solution, but to be completely honest, I don't understand PHP well enough to know what to do with it  I know that these are really basic questions, but here is what I can't figure out:

 

1) Am I right in assuming that I need to replace the $t with the name of my array and then leave the rest as it is?

 

2) WHERE in my code do I put all of this? Does it matter where it goes? And is there an easy way to figure it out without me having to post all of my script?

 

3) Ok this may be the dumbest question yet, but how do I know what the name of my array is? I am fairly certain it is one of three things - but I have a hard time telling for sure because everything seems to get relabeled (if that even makes any sense). Here is a snippet of my code that I *think* my array name is in. I am not sure if this is even helpful, but just in case:

 

foreach($variablen as $name=>$value) {

mysql_query ("ALTER TABLE $table ADD `$name` VARCHAR(15)");

 

I would really appreciate some help with this. I have been trying to figure out a solution for HOURS now and I am just totally lost. I know I will get more savvy with this as time progresses, but right now I really just need my site to work.

 

Thanks so much!!!

[JasonLewis]

Your better off just using mysql_real_escape() on variables that are going to be inputted into a database. There is no point running it on every single variable sent by GET and POST.

[Lilly]

I am not sure how that will work... let me explain.

 

My code automatically creates a table in MySQL based on the data submitted from a form. So, at no point in my PHP code do I use any specific variable names - the code just refers to this array over and over again.

 

Can I still use the mysql_escape_string with specific variables even in they are not listed anywhere else in my PHP code? Sorry again if this is a silly question - I am still learning

[darkfreaks]

we need to see the full code please where is the actual array ???

[darkfreaks]

why not do something like:

 

<?php
$connect=mysqli_connect('localhost','user','pass','db');
$stmt=$connect->prepare("ALTER TABLE ? ADD `?` VARCHAR(15)");
$stmt=bind_param('ss',$table,$name);
$stmt->execute();
$stmt->close();
?>

 

this will weed out most MYSQL injection

[Lilly]

Ok, so here is my code - sorry I didn't post it before - it is so long and I wasn't sure if it would be useful. Also, darkfreaks thanks for the suggestion - but can you explain it a little more? Where would I incorporate those statements into the code I already have. THANK YOU so much for your help - I can't even tell you how nice it is to have a little help!

 

<?php

$user="xxxx"; 
$password="xxxx"; 

$host="xxxx"; 
$database="xxxx"; 
$table="test"; 

$allfieldsfull=true; 
$errormessage='Please fill in all the fields!';
$order=true; 

$thank_you_text='Thank you! Your answers have been saved.';

$variablen = (!empty($_POST)) ?  $_POST : $_GET;  

if (empty ($variablen)) {
echo "There is no form input to be processed."; exit; }

$identification = $variablen['identification'];
$counter = $variablen['counter'];

if (!isset ($identification)) 
{
$referer=$_SERVER['HTTP_REFERER'];
$referer = rtrim ($referer,"/ \t\n\r\0\x0B.");
}

if ($allfieldsfull) {	
foreach($variablen as $name=>$value)
	 { if ($value == "") { 	echo $errormessage;
	 						echo '<br><br><a href="javascript:history.back()"><---</a>';
	 						exit;	}
	 }					}

if ($order) {ksort ($variablen);}
$next_page = $variablen['next_page'];
$counter_page = ++$counter+1;

mysql_connect($host,$user,$password) or die( "Unable to connect to database server<br>".mysql_error());

if (!isset ($identification)) 
{mysql_query("CREATE DATABASE $database"); }

mysql_select_db($database) or die( "Unable to select database<br>".mysql_error());

if (!isset ($identification)) {
mysql_query ("CREATE TABLE $table (`identification` int(9) NOT NULL auto_increment, 
`page1` TEXT, `participation_date` DATE, `time_submit1` VARCHAR(, `ip_number` VARCHAR(15), 
`browser` TINYTEXT, PRIMARY KEY  (`identification`)) TYPE=MyISAM");
}

//change array, so that time_submit and page are renamed dynamically
foreach($variablen as $name=>$value){ 
if ($name == "next_page") { $name = "page".$counter_page; }
elseif ($name == "counter") {$name = "time_submit".$counter; $value = date("G:i:s"); }
$newarray[$name]=$value; } 
$variablen = $newarray; 

//for each line in the array of submitted variables do the following (traverse array)
foreach($variablen as $name=>$value) {	
	//modify table step by step (add colums according to html input)
	mysql_query ("ALTER TABLE $table ADD `$name` VARCHAR(15)");
 								}

if (!isset ($identification)) 		{
//insert new record into db table (into the referer field) and thus generate identifcation (new record)
mysql_query("INSERT INTO $table (page1, participation_date, time_submit1, ip_number, browser) 
VALUES ('$referer', '".date("Y-m-d")."', '".date("G:i:s")."', 
'".$_SERVER['REMOTE_ADDR']."', '".$_SERVER['HTTP_USER_AGENT']."')")or die( "Unable to insert into table!<br>".mysql_error()); 
//grab last value of auto-increment variable "identification" to be used as identifier
$identification = mysql_insert_id(); }

//for each line in the array of submitted variables do the following
foreach($variablen as $name=>$value)	 {
	   //echo $name." - ".$value."<br>"; //spits out array for control purposes
		//update db table step by step
		mysql_query("UPDATE $table SET `$name`='$value' WHERE identification=$identification") or die( "<br><br>Error>".mysql_error()); 
										 }
//close connection
mysql_close();

if (!isset ($next_page)) {echo $thank_you_text; }
else 	{ //call up next HTML page and pass on ID and counter
echo "<html><head></head><body onLoad=\"javascript:location.replace('".$next_page."?op56=".$identification."&nr93=".$counter."')\">
<a href=\"".$next_page."?op56=".$identification."&nr93=".$counter."\">Next Page</a></body></html>"; 
//echo "<html><head></head><body><a href=\"".$next_page."?op56=".$identification."&nr93=".$counter."\">Next Page</a></body></html>"; 
}
?>

 

 

[darkfreaks]

Like This:

 

<?php
$user="xxxx"; 
$password="xxxx"; 

$host="xxxx"; 
$database="xxxx"; 
$table="test"; 

$allfieldsfull=true; 
$errormessage='Please fill in all the fields!';
$order=true; 

$thank_you_text='Thank you! Your answers have been saved.';

$variablen = (!empty($_POST)) ?  $_POST : $_GET;  

if (empty ($variablen)) {
echo "There is no form input to be processed."; exit; }

$identification = $variablen['identification'];
$counter = $variablen['counter'];

if (!isset ($identification)) 
{
   $referer=$_SERVER['HTTP_REFERER'];
   $referer = rtrim ($referer,"/ \t\n\r\0\x0B.");
}

if ($allfieldsfull) {   
foreach($variablen as $name=>$value)
       { if ($value == "") {    echo $errormessage;
                         echo '<br><br><a href="javascript:history.back()"><---</a>';
                         exit;   }
       }               }

if ($order) {ksort ($variablen);}
$next_page = $variablen['next_page'];
$counter_page = ++$counter+1;
$connect=mysqli_connect($host,$user,$password) or die( "Unable to connect to database server<br>".mysqli_error());

if (!isset ($identification)) 
{mysqli_query("CREATE DATABASE $database"); }

mysqli_select_db($database) or die( "Unable to select database<br>".mysqli_error());

if (!isset ($identification)) {
mysql_query ("CREATE TABLE $table (`identification` int(9) NOT NULL auto_increment, 
`page1` TEXT, `participation_date` DATE, `time_submit1` VARCHAR(, `ip_number` VARCHAR(15), 
`browser` TINYTEXT, PRIMARY KEY  (`identification`)) TYPE=MyISAM");
}

//change array, so that time_submit and page are renamed dynamically
foreach($variablen as $name=>$value){ 
if ($name == "next_page") { $name = "page".$counter_page; }
elseif ($name == "counter") {$name = "time_submit".$counter; $value = date("G:i:s"); }
$newarray[$name]=$value; } 
$variablen = $newarray; 

//for each line in the array of submitted variables do the following (traverse array)
foreach($variablen as $name=>$value) {   
      //modify table step by step (add colums according to html input)
      mysqli_query ("ALTER TABLE $table ADD `$name` VARCHAR(15)");
                            }

if (!isset ($identification))       {
//insert new record into db table (into the referer field) and thus generate identifcation (new record)
$stmt=$connect->prpare("INSERT INTO ? (page1, participation_date, time_submit1, ip_number, browser) 
VALUES ('?', '?', '?', 
'?', '?')");
$stmt=param_bind('ssdddd',$table,$referer,date("Y-m-d"),date("G:i:s"),$_SERVER['REMOTE_ADDR'],
$_SERVER['HTTP_USER_AGENT']);
$stmt->execute();
$stmt->close();

//grab last value of auto-increment variable "identification" to be used as identifier
$identification = mysqli_insert_id(); }

//for each line in the array of submitted variables do the following
   foreach($variablen as $name=>$value)    {
         //echo $name." - ".$value."<br>"; //spits out array for control purposes
         //update db table step by step
 $stmt=$connect->prepare("UPDATE ? SET `?`='?' WHERE identification=?")
         $stmt=param_bind('sssi',$table,$name,$value,$identification);  
 $stmt->execute();
 $stmt->close();
   
   
   }
//close connection
mysqli_close();

if (!isset ($next_page)) {echo $thank_you_text; }
else    { //call up next HTML page and pass on ID and counter
echo "<html><head></head><body onLoad=\"javascript:location.replace('".$next_page."?op56=".$identification."&nr93=".$counter."')\">
<a href=\"".$next_page."?op56=".$identification."&nr93=".$counter."\">Next Page</a></body></html>"; 
//echo "<html><head></head><body><a href=\"".$next_page."?op56=".$identification."&nr93=".$counter."\">Next Page</a></body></html>"; 
}
?>

[Lilly]

I tried the code you suggested and it doesn't seem to be working. I am not getting an error messages or anything - when I submit the form I just get a completely blank page...

 

Any ideas as to what might be happening?

[Daniel0]

My code automatically creates a table in MySQL based on the data submitted from a form.

 

Don't do that. I'm not sure what you're trying to do, but there is definitely a better way.

[Lilly]

DanielO - I don't quite understand your comment. Can you please elaborate? Why wouldn't I want to use the code I have?

 

You can see the entirety of my code in an above post if it helps for you to figure out what I am doing. Also, here is a basic summary:

 

I am trying to collect numeric and text responses to questions I ask of users. I am not very familiar with PHP or MySQL so a colleague in the area I work in created this code so that I could just use it for any form I created and not really have to change anything (except connection info like the table name). So basically, the point of the code was to make everything automatic (e.g. table creation, data insertion, etc) so that a novice like me could easily collect the data I need.

 

It has been working very well over the past year or so. I only started having problems when I started to collect text responses (instead of just numeric responses) because then users started to use apostrophe's in their entries which is creating a big problem for me.

 

ANY and ALL help is greatly appreciated.

[Daniel0]

Well, that's just not the way tables are meant to be used. Imagine you got a million responses. That would mean a million different tables with only one row in each. Surely you can see the problem with that.

 

Also check how how many queries you are executing to insert ONE row.

 

It doesn't scale at all, it isn't normalized and it will be a rather complex task retrieving all the responses.

[Lilly]

Ok, I see - I obviously did not do a good job in describing what the code does.

 

If a user is the FIRST person to submit data for a particular table, the table will automatically be created. Then for each person after that who submits data, the data is appended to the table that was for the first user. Does that make more sense?

 

I have used it before and it definitely only creates one table with multiple rows in each table that corresponds to how many users submitted data.

 

I can see why the alternative you were thinking of would be a big waste

 

This still leaves me with the problem of how to mysql_escape_string with an array... Any ideas?

[Daniel0]

Well, it's still a bad idea. Tables aren't meant to be created on the fly like that.

 

You could have a database schema like this:

CREATE TABLE `surveys` (
  `survey_id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `description` text,
  PRIMARY KEY (`survey_id`)
) ENGINE=InnoDB;

CREATE TABLE `questions` (
  `question_id` int(10) unsigned NOT NULL auto_increment,
  `survey_id` int(10) unsigned NOT NULL,
  `question` varchar(100) NOT NULL,
  PRIMARY KEY (`question_id`),
  CONSTRAINT `questions_ibfk_1` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`survey_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE `responses` (
  `response_id` int(10) unsigned NOT NULL auto_increment,
  `question_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `response` text NOT NULL,
  PRIMARY KEY (`response_id`),
  CONSTRAINT `responses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `responses_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

 

And then here is some sample data:

INSERT INTO `surveys` (`survey_id`, `title`, `description`) VALUES
(1, 'Quick test survey', 'Just a quick test to illustrate usage.');

INSERT INTO `questions` (`question_id`, `survey_id`, `question`) VALUES
(1, 1, 'Where do you live?'),
(2, 1, 'How is the weather outside?');

INSERT INTO `users` (`user_id`, `name`) VALUES
(1, 'Daniel'),
(2, 'John Doe');

INSERT INTO `responses` (`response_id`, `question_id`, `user_id`, `response`) VALUES
(1, 1, 1, 'Denmark'),
(2, 2, 1, 'It''s okay right now.'),
(3, 1, 2, 'USA'),
(4, 2, 2, 'It''s raining.');

 

And getting data could be like this:

SELECT u.name, q.question, r.response
  FROM surveys AS s
  INNER JOIN questions AS q
    ON s.survey_id = q.survey_id
  INNER JOIN responses AS r
    ON q.question_id = r.question_id
  INNER JOIN users AS u
    ON r.user_id = u.user_id
  WHERE s.survey_id = 1
  ORDER BY u.user_id, q.question_id;

 

Which would result in something like this:

+----------+-----------------------------+----------------------+
| name     | question                    | response             |
+----------+-----------------------------+----------------------+
| Daniel   | Where do you live?          | Denmark              |
| Daniel   | How is the weather outside? | It's okay right now. |
| John Doe | Where do you live?          | USA                  |
| John Doe | How is the weather outside? | It's raining.        |
+----------+-----------------------------+----------------------+

 

 

This is better and is normalized. You won't have any anomalies in the data, you have constraints to ensure data integrity, it scales better and will be much more efficient.

[Lilly]

DanielO: To be completely honest, I have no idea what you are talking about in your response. I appreciate the help but you are going way beyond my level of knowledge there... and I am not quite sure how your solution is related to the actual problem I am having

 

I totally feel like crying... ok modify that - I am crying... why can't someone just help me solve the problem I am having given the code I am currently using. I know it is not the best "most efficient" code but it is all I have and I don't understand this stuff enough to try and use anything else.

 

I know there are a lot of things I will be able to worry about/improve upon once I understand this stuff better, but right now I just need a solution so I don't have to continue to lose out on a whole bunch of user input.

[Daniel0]

Well, try this:

<?php
$user="xxxx"; 
$password="xxxx"; 

$host="xxxx"; 
$database="xxxx"; 
$table="test"; 

$allfieldsfull=true; 
$errormessage='Please fill in all the fields!';
$order=true; 

$thank_you_text='Thank you! Your answers have been saved.';

if (!count($_POST)) {
echo "There is no form input to be processed.";
exit;
}

$identification = $_POST['identification'];
$counter = $_POST['counter'];

if (empty($identification)) {
   $referer = rtrim($_SERVER['HTTP_REFERER'],"/ \t\n\r\0\x0B.");
}

if ($allfieldsfull) {   
foreach($variablen as $name => $value) {
	if (empty($value)) {
		echo $errormessage;
            echo '<br><br><a href="javascript:history.back()"><---</a>';
            exit;   
	}
}
}

if ($order) {
ksort($_POST);
}

$next_page = $_POST['next_page'];
$counter_page = ++$counter+1;

mysql_connect($host,$user,$password) or trigger_error("Unable to connect to database server: " . mysql_error(), E_USER_ERROR);

if (!empty($identification)) {
mysql_query("CREATE DATABASE $database");
}

mysql_select_db($database) or trigger_error("Unable to select database: " . mysql_error(), E_USER_ERROR);

if (!isset($identification)) {
mysql_query("CREATE TABLE $table (`identification` int(9) NOT NULL auto_increment, 
`page1` TEXT, `participation_date` DATE, `time_submit1` VARCHAR(, `ip_number` VARCHAR(15), 
`browser` TINYTEXT, PRIMARY KEY  (`identification`)) TYPE=MyISAM") or trigger_error("Error: " . mysql_error(), E_USER_ERROR);
}

//change array, so that time_submit and page are renamed dynamically
foreach($_POST as $name => $value) { 
if ($name == "next_page") {
	$name = "page" . $counter_page;
}
else if ($name == "counter") {
	$name = "time_submit" . $counter;
	$value = date("G:i:s");
}
$newarray[$name] = $value;
} 
$variables = $newarray; 

//for each line in the array of submitted variables do the following (traverse array)
foreach($variables as $name => $value) {   
      //modify table step by step (add colums according to html input)
      mysql_query("ALTER TABLE $table ADD `" . mysql_real_escape_string($name) ."` VARCHAR(15)");
}

if (empty($identification)) {
//insert new record into db table (into the referer field) and thus generate identifcation (new record)
$referer = mysql_real_escape_string($referer);
$ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
$useragent = mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']);
$date = date('Y-m-d');
$time = date('G:i:s');

$query = "INSERT INTO {$table} (page1, participation_date, time_submit1, ip_number, browser) VALUES ('{$referer}', '{$date}', '{$time}', '{$ip}', '{$useragent}')";
mysql_query($query) or trigger_error("Unable to insert into table: " . mysql_error(), E_USER_ERROR);

//grab last value of auto-increment variable "identification" to be used as identifier
$identification = mysql_insert_id();
}

$identification = mysql_real_escape_string($identification);

//for each line in the array of submitted variables do the following
foreach($variables as $name=>$value) {
//echo $name." - ".$value."<br>"; //spits out array for control purposes
//update db table step by step

$name = mysql_real_escape_string($name);
$value = mysql_real_escape_string($value);

mysql_query("UPDATE $table SET `{$name}`='{$value}' WHERE identification='{$identification}'") or trigger_error("Error: " . mysql_error(), E_USER_ERROR); 
}

//close connection
mysql_close();

if (!isset($next_page)) {
echo $thank_you_text;
}
else { //call up next HTML page and pass on ID and counter
echo "<html><head></head><body onLoad=\"javascript:location.replace('".$next_page."?op56=".$identification."&nr93=".$counter."')\">
      <a href=\"".$next_page."?op56=".$identification."&nr93=".$counter."\">Next Page</a></body></html>"; 
}
?>

 

Really though, I don't think your colleague knows what (s)he is doing. I don't hope his/her job is programming. Tell that person to look up these things: Database normalization, input sanitation, error handling, separation of concerns, formatting and indent styling (the code was nearly unreadable due to poor formatting).

[Lilly]

OMG DanielO you are AMAZING - it WORKED!!!!!!

 

I am so thankful that you added pieces of code to my existing code - you have no idea!

 

I am no longer in contact with the person who wrote the original code - but don't worry she is not a professional programmer. 

 

I am going to personally look up the topics you suggested in my quest to learn more about programming. Hopefully reading up on those topics and looking at the "messy" code I  have can be a good learning tool.

 

Thanks so much for working with me on what I have -this really made my day/week/month