Inserting Data that Contains Text

PCHome · March 31, 2025, 3:25am

I’m not sure what code to post as this was working and now it’s not but there is a lot of code and I can’t find where it’s breaking. I changed the processing from mysqli to prepared statements but the issue is not related to that. This is being processed from a POST and the values are filtered in various ways as needed for special fields just as they were doing before. However, it’s the fields that contain text and line breaks (and occasional HTML) where the problem is as it seems to want to process each line in the text individually! Below is the error which I understand now that I know what it’s doing but not sure why it’s doing it. The statement should indeed have 5 elements but after it breaks up the text it ends up with giving 45 values!

The code is used for many different tables with different column names so is there a way to make it ignore carriage returns? I checked the text itself to be sure that there were no odd characters but it has just regular line breaks.

mysqli_stmt::execute(): Argument #1 ($params) must consist of exactly 5 elements, 45 present

It’s somewhere in here:

	// PREPARE AND FILTER FIELDS FOR PROCESSING
	if (isset($_POST)) :
		$Values = [];
		if ($processType !== "") :
			if ($processType === 'insert') unset($_POST['insert']);
			if ($processType === 'copy') unset($_POST['copy']);
			if ($processType === 'update') unset($_POST['update']);
			if ($processType === 'update') unset($_POST['delete']);

			// REMOVE ANY UNNEEDED FIELDS SPECIFIED IN $RemoveFields ARRAY
			// EXAMPLE $RemoveFields = ['ZipCode', 'Address2'];
			if (isset($RemoveFields) && !is_array($RemoveFields)) $RemoveFields = [$RemoveFields];
			$filteredarray = (isset($RemoveFields)) ? array_diff_key($_POST, array_flip($RemoveFields)) : $_POST;
			array_unique($filteredarray);

			// FOR INSERT OR COPY, REMOVE THE ID FIELD
			if ($processType === 'insert' || $processType === 'copy') unset($Values['ID']);

			// FILTER AND PREPARE VALUES
			foreach ($filteredarray as $key=>$value) :
				// Do not process security codes
				if ($key === 'confirm') continue;
				if ($key === 'nonce') continue;
				// DO NOT PROCESS LOGIN FORM
				if ($key === 'DoLogin') continue;
				// DO NOT PROCESS CAPTCHA VERIFYCODE FIELDS
                if ($key === 'VerifyCode') continue;
				if ($key === 'captcha_challenge') continue;
				// DO NOT PROCESS ID FIELD
				// if ($key === 'ID') continue;

				// PROCESS ANY PASSWORD FIELD
				if (Contains("Pass", $key)) :
					// ENCODE PASSWORD FIELD
					if ($value !== "") $value=md5($value);
					// IF NO CHANGES, KEEP ORIGINAL PASSWORD
					if ($value === "") continue;
				endif;

				// PROCESS DATES/TIMES
				if (Contains("Date", $key)) :
					// PROCESS TO UNIX TIMESTAMP
					if (is_numeric($value) && $value < 10000) :
						// PROCESS SIX SELECTOR DATE AND TIME FIELDS
						if ($DateFieldType === "six2Unix" ) :
							$removals = ['year','month','day','hour','minute','second'];
							// PROCESS TWO SELECTOR DATE AND TIME FIELDS
						elseif ($DateFieldType === "two2Unix" || $DateFieldType === "two2MySQL") :
							$removals = ['date','time'];
						endif;
						$FieldName = trim(str_replace($removals,"",$key));
						$value = dateProcess($FieldName,$DateFieldType);
						foreach ($removals as $piece) :
							if (isset($_POST[$piece.$FieldName])) continue;
						endforeach;
						$key = $FieldName;
					// PROCESS MySQL DATETIME FIELDS TO Unix or MySQL
					elseif (!is_numeric($value)) :
						if ($DateFieldType === "picker2Unix") : // CALENDAR-TYPE DATE ONLY PICKER TO UNIX
							$value = dateProcess($key,$DateFieldType);
						elseif ($DateFieldType === "pickertime2MySQL") : // CALENDAR-TYPE DATE PICKER AND TIME SELECTOR TO MYSQL
							$value = dateProcess($key,$DateFieldType);
						endif;
					endif;
				endif;

				// PROCESS LATITUDE AND LONGITUDE FROM ADDRESS
				if ($key === "Address") :
					$latlng = locationFromAddress($value);
					$latlng = explode(',',$latlng);
				endif;

				if ($key === "Lat" && isset($latlng[0])) $value = $latlng[0];
				if ($key === "Lng" && isset($latlng[1])) $value = $latlng[1];

				// PREPARE ARRAY FOR QUERY
				$Values[$key] = $value;
			endforeach;

			$Values = array_unique($Values);

			// REMOVE EMPTY VALUES
			$Values = array_filter($Values);

$insertVals = join(',',$Values);

phdr · March 31, 2025, 4:44am

What does using var_dump() or print_r() on both $_POST and $Values show? The code is just looping over the keys and values in the $_POST data (which in itself is a bad idea since hackers can submit anything they want.) If you end up with 45 entries in the $Values array, that means there are 45 keys/values in $_POST that were not filtered out.

I recommend two fundamental changes -

Use the much simpler and better designed PDO extension. The mysqli extension is overly complicated and full of special case uses that are not general purpose. For example, the array of values you supply to the ->execute() call are all treated as strings, and don’t work with values that must be numeric, such as in a LIMIT … clause, don’t work with boolen values, and don’t work with null values.
Take a proactive approach, where you define an array of the expected fields and their characteristics for any operation, rather than a reactive approach of trying to remove or filter out the things that you don’t want, which will miss things that hackers add to the submitted data, and depending on your code building the query will allow sql injection via any hacker supplied field name you use in the query.

Also -

The $_POST array is always set, even if it is empty. You should instead detect if $_SERVER['REQUEST_METHOD'] === 'POST' and then test if the $_POST array is not empty. If the post data size exceeds the post_max_size setting, both the $_POST and $_FILES arrays will be empty. Your code must handle this case.
md5() was never intended for password hashing. Use php’s password_hash() and password_verify() instead.
Applying array_unque() to the array of values doesn’t make sense. What if some field = ‘abc’ and some other field = ‘abc’. This will completely remove a field and its value.
Likewise, using array_filter() will prevent you from inserting/updating a field with a numerical zero or an empty string.

PCHome · March 31, 2025, 5:02am

As I rewrite this, some things are not yet done such as determining the post method but it is being submitted for these tests. I have done var_dump() after each line that modifies the data and the data looks proper. A separate function is generating the prepared statement and is also doing so properly. I’m not new PHP which is why the mystery so I think it’s something simple that I missed that I was hoping another set of eyes can spot.

phdr · March 31, 2025, 5:22am

Since you haven’t shown all the code necessary to reproduce the problem, nor what the input data is and what the $Values data is, no other eyes have seen anything upon which to help.

PCHome · March 31, 2025, 5:37am

$Values is generated from the post and is all in the posted code but showing all the code would be impossible as much of it is in other functions. However, I think the issue is that the text field in question is not getting quoted and not sure how to do that when the column name is not always the same.

phdr · March 31, 2025, 5:49am

Prepared queries do NOT add quotes to variables holding text. For each ? place-holder you put into the sql query statement, you must provide one value/variable in the array of values in the ->execute() call.

PCHome · March 31, 2025, 6:16am

I know that that prepared statements don’t add quotes but doesn’t text being passed into it need to be quoted? Otherwise, how are the fields separated from one another? This text includes all sorts of punctuation including commas so I can’t imagine it not needing something unless one of the filters is somehow breaking the text apart at them prior to the query being run.

phdr · March 31, 2025, 6:31am

Cannot help you without the code.

The $Values array contains the form field names, as the keys, and the submitted field values. All you need to do is supply the array of $Values to the ->execute() call, assuming that they are in the same order as the place-holders are in the sql query statement.

The only time you may need to do anything extra to the values is if you are doing a LIKE comparison in a query with wild-card characters.

PCHome · March 31, 2025, 6:17pm

After my last reply and I had signed off, I realized I had just answered my own question. If you look at the very last line of my code, the data is being exploded using join() at commas which is breaking up the text wherever there is one! The data should be sent as an array, probably without named keys as it is now, so that’s the fix.