Jump to content

Outputting MySql data that contains ' into php variables


Adamhumbug

Recommended Posts

Hi All,

I have done a select from my DB and the data contains a '.

echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='" . $uid . "' href='#userModal' data-firstname='" . $ufn ."' data-lastname='". $uln."' data-email='" . $ue . "' data-accountlevel='" . $ualid . "' data-mobile='".$um ."'data-role='".$urid."' data-active-sheets='".$ename."'>Manage</a></td>";

outputs

<a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="2" href="#userModal" data-firstname="Chelsea" data-lastname="Hockley" data-email="chelsea@hotmail.com" data-accountlevel="1" data-mobile="0774882" data-role="1" data-active-sheets="A new event,Chelsea" s="" event'="">Manage</a>

the issue part

data-active-sheets="A new event,Chelsea" s="" event'=""

Should be

data-active-sheets="A new event,Chelsea's event"

How do i prevent the ' from causing me this issue?

Link to comment
Share on other sites

@Adamhumbug What you have posted is impossible and, if the problem is even remotely as you say it is, has nothing to do with a ' character.

You show that you are outputting the content using

[ic]echo "<td><a class='btn btn-primary col-sm-12' . . . [/ic]

Where the string is defined using double-quotes and the parameters of the tags are in single quotes. But, then you show that output like this:

[ic]<a class="btn btn-primary col-sm-12" . . . [/ic]

Where the parameters are in double quotes. That is not possible. And it would make  HUGE difference in the output based on if the variables have single/double quotes

 

Secondly, you state that the value causing the problem is due to a ' character. That is also not possible. If this

[ic]data-active-sheets='".$ename."'>Manage</a></td>";[/ic]

Produces this:

[ic]data-active-sheets="A new event,Chelsea" s="" event'="">Manage</a>[/ic]

Then the value of $ename is A new event,Chelsea" s="" event'=

Link to comment
Share on other sites

One of things I really liked about PHP, having previously used VB (ugh!) was the ability to directly embed variables into strings without all the confusing quoting and concatenating. Coupled with the ability to use HEREDOC syntax life became much easier when confronted with strings like this one.

I would suggest...

echo <<<TEXT
<td><a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="$uid" href="#userModal" data-firstname="$ufn" data-lastname="$uln" data-email="$ue" data-accountlevel="$ualid" data-mobile="$um" data-role="$urid" data-active-sheets="$ename">Manage</a></td>
TEXT;

Alternatively, go for a string inside "...." and with single quotes around attribute values, BUT, where you have an attribute value that could contain a single quote character or apostrophe, use escaped double quotes. EG

echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='$uid' href='#userModal' data-firstname=\"$ufn\" data-lastname=\"$uln\" data-email='$ue' data-accountlevel='$ualid' data-mobile='$um' data-role='$urid' data-active-sheets=\"$ename\">Manage</a></td>";

 

Edited by Barand
  • Great Answer 1
Link to comment
Share on other sites

15 hours ago, Psycho said:

@Adamhumbug What you have posted is impossible and, if the problem is even remotely as you say it is, has nothing to do with a ' character.

You show that you are outputting the content using

[ic]echo "<td><a class='btn btn-primary col-sm-12' . . . [/ic]

Where the string is defined using double-quotes and the parameters of the tags are in single quotes. But, then you show that output like this:

[ic]<a class="btn btn-primary col-sm-12" . . . [/ic]

Where the parameters are in double quotes. That is not possible. And it would make  HUGE difference in the output based on if the variables have single/double quotes

 

Secondly, you state that the value causing the problem is due to a ' character. That is also not possible. If this

[ic]data-active-sheets='".$ename."'>Manage</a></td>";[/ic]

Produces this:

[ic]data-active-sheets="A new event,Chelsea" s="" event'="">Manage</a>[/ic]

Then the value of $ename is A new event,Chelsea" s="" event'=

Hi Psyco,

echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='" . $uid . "' href='#userModal' data-firstname='" . $ufn ."' data-lastname='". $uln."' data-email='" . $ue . "' data-accountlevel='" . $ualid . "' data-mobile='".$um ."'data-role='".$urid."' data-active-sheets='".$ename."'>Manage</a></td>";

This is copied directly from my code

1738610673_Screenshot2019-12-18at09_17_19.png.07ffabb7a664d935758149c620d3b248.png

This is copied from inspector in Firefox for mac.

When i take the ' out of Chelsea's

i get the following

<a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="2" href="#userModal" data-firstname="Chelsea" data-lastname="Hockley" data-email="chelseahockley@hotmail.com" data-accountlevel="1" data-mobile="07783544882" data-role="1" data-active-sheets="A new event, Chelseas Event">Manage</a>

 

Edited by Adamhumbug
Link to comment
Share on other sites

15 hours ago, Barand said:

One of things I really liked about PHP, having previously used VB (ugh!) was the ability to directly embed variables into strings without all the confusing quoting and concatenating. Coupled with the ability to use HEREDOC syntax life became much easier when confronted with strings like this one.

I would suggest...


echo <<<TEXT
<td><a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="$uid" href="#userModal" data-firstname="$ufn" data-lastname="$uln" data-email="$ue" data-accountlevel="$ualid" data-mobile="$um" data-role="$urid" data-active-sheets="$ename">Manage</a></td>
TEXT;

Alternatively, go for a string inside "...." and with single quotes around attribute values, BUT, where you have an attribute value that could contain a single quote character or apostrophe, use escaped double quotes. EG


echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='$uid' href='#userModal' data-firstname=\"$ufn\" data-lastname=\"$uln\" data-email='$ue' data-accountlevel='$ualid' data-mobile='$um' data-role='$urid' data-active-sheets=\"$ename\">Manage</a></td>";

 

Hi Barand, great help as ever.

When i make the change using your first suggestion i get the following error.


Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /homepages/29/d742272110/htdocs/user-list.php on line 132
 

$sql = "SELECT role_id, role_name FROM ssm_role ORDER BY role_name ASC";
	if($result = mysqli_query($conn, $sql)){
		if (mysqli_num_rows($result)>0){
			while ($row = mysqli_fetch_array($result)){
				echo "<option value='".$row['role_id']."'>".$row['role_name']."</option>"; //line 132
			}
		}
	}

I am in the process of changing all my php to use prepared stmts but have not got to this one yet.

Link to comment
Share on other sites

Strange!. Line 132 doesn't give a problem for me. However, I would write it differently, removing the concatenation by using {..}s around the array variables

echo "<option value='{$row['role_id']}'>{$row['role_name']}</option>";

Alternatively, as constants are not permitted within a string, the single quotes around the keys can be omitted

echo "<option value='$row[role_id]'>$row[role_name]</option>"; 

 

42 minutes ago, Adamhumbug said:

I am in the process of changing all my php to use prepared stmts but have not got to this one yet

That one doesn't need preparing - query() is fine. No parameters.

Link to comment
Share on other sites

10 minutes ago, Barand said:

Strange!. Line 132 doesn't give a problem for me. However, I would write it differently, removing the concatenation by using {..}s around the array variables


echo "<option value='{$row['role_id']}'>{$row['role_name']}</option>";

Alternatively, as constants are not permitted within a string, the single quotes around the keys can be omitted


echo "<option value='$row[role_id]'>$row[role_name]</option>"; 

 

That one doesn't need preparing - query() is fine. No parameters.

Yeah its very strange, when i change that one section to use <<<TEXT

several areas of my code are now throwing


Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /homepages/29/d742272110/htdocs/user-list.php on line 200
 

I had it on this line

echo "<option value='{$row['user_level_id']}'>{$row['user_level_name']}</option>";

which was concatenated and i have now chnaged

but i am now getting it on this line

<?php
					$uid = $_SESSION['user_id']; //THIS LINE
					$stmt = $conn->prepare("SELECT user_firstname, user_lastname FROM ssm_user WHERE user_id = ?");
					$stmt -> bind_param('i', $uid);
					$stmt -> execute();
					$stmt -> store_result();
					$stmt -> bind_result($fname, $lname);
					$stmt -> fetch();
				?>

 

Link to comment
Share on other sites

Just now, Barand said:

It could be that the real cause is higher up in the code but isn't being noticed until the reported line.

Post the code from your <<<TEXT to the error line.

					if($_SESSION['user_level']=='100'){
					echo <<<TEXT
					<td><a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="$uid" href="#userModal" data-firstname="$ufn" data-lastname="$uln" data-email="$ue" data-accountlevel="$ualid" data-mobile="$um" data-role="$urid" data-active-sheets="$ename">Manage</a></td>
					TEXT;
					}
				}
					echo "</tr>
					</tbody></table>";
				
				?>
			</div>
<!-- User Modal -->
<div class="modal fade" id="userModal">
	<div class="modal-dialog">
		<div class="modal-content">
			<div class="modal-header">
				<h4 class="modal-title" id="userModalLabel">Users</h4>
				<button type="button" class="close" data-dismiss="modal" >
				<span >&times;</span>
				</button>
			</div>
			<form method="POST" id="updateUserSubmit">
				<div class="modal-body">
					
					<ul class="nav nav-tabs mb-3 ml-3 mr-3">
						<li class="nav-item"><a class="nav-link active" href="#tab1" data-toggle="tab">User Info</a></li>
						<li class="nav-item"><a class="nav-link" href="#tab2" data-toggle="tab">Log On Info</a></li>
					</ul>
					<div class="tab-content">
						<div class="tab-pane active" id="tab1">
							<input type="hidden" name="UM-uid" id="UM-uid">
							<div class="md-form">
								<div class="col-md-12 mb-3">
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">First Name</span>
										</div>
										<input name="UM-firstName" id="UM-firstName" type="text" class="form-control" placeholder="First Name" required>
									</div>
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">Last Name</span>
										</div>
										<input name="UM-lastName" id="UM-lastName" type="text" class="form-control" placeholder="Last Name" required>
									</div>
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">Mobile Number</span>
										</div>
										<input name="UM-mobileNumber" id="UM-mobileNumber" type="text" class="form-control" placeholder="Mobile Number" required>
									</div>
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">Email Address</span>
										</div>
										<input name="UM-emailAddress" id="UM-emailAddress" type="text" class="form-control" placeholder="Email Address" required>
									</div>
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">Role</span>
										</div>
										<select name="UM-roleId" id="UM-roleId" class="custom-select">
											<?php
											
											$sql = "SELECT role_id, role_name FROM ssm_role ORDER BY role_name ASC";
											if($result = mysqli_query($conn, $sql)){
												if (mysqli_num_rows($result)>0){
													while ($row = mysqli_fetch_array($result)){
														echo "<option value='{$row['role_id']}'>{$row['role_name']}</option>";
													}
												}
											}
											?>
											
										</select>
									</div>
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">User Level</span>
										</div>
										<select name="UM-accountLevelId" id="UM-accountLevelId" class="custom-select">
											<?php
											echo "<option value ='0'>Please Select</option>";
											$sql = "SELECT user_level_id, user_level_name, user_level_value From ssm_user_level ORDER BY user_level_name ASC";
											if($result = mysqli_query($conn, $sql)){
												if (mysqli_num_rows($result)>0){
													while ($row = mysqli_fetch_array($result)){
														echo "<option value='{$row['user_level_id']}'>{$row['user_level_name']}</option>";
													}
												}
											}
											?>
											
										</select>
									</div>
									
								</div>
							</div>
						</div>
						<div class="tab-pane" id="tab2">
							<div class="md-form">
								<div class="col-md-12 mb-3">
									<div class="input-group mb-3">
										<div class="input-group-prepend">
											<span class="input-group-text modalprew">New Password</span>
										</div>
										<input name="UM-pass" id="UM-pass" type="text" class="form-control" placeholder="New Pass">
									</div>
								</div>
							</div>
						</div>
					</div>
				</div>
				<div class="modal-footer">
					<button id="updateUser" formaction="actions/update-user-action.php" type="submit" class="btn btn-success">Update User</button>
					<a data-toggle="modal" data-userid="" href='#userDeleteConfirmationModal' id="callDeleteUserConf" class="btn btn-danger">Delete User</a>
				</div>
			</form>
		</div>
	</div>
</div>
<!-- End Of User Modal -->


<!-- Start of modal -->
<div class="modal fade" id="userDeleteConfirmationModal" tabindex="-1" role="dialog">
	<div class="modal-dialog" >
		<div class="modal-content">
			<div class="modal-header">
				<h4 class="modal-title" id="deleteUserConfLabel">Delete - </h4>
				<button type="button" class="close" data-dismiss="modal" >
				<span >&times;</span>
				</button>
			</div>
			<form method="POST" id="deleteUserConfForm">
				<?php
					$uid = $_SESSION['user_id'];
					$stmt = $conn->prepare("SELECT user_firstname, user_lastname FROM ssm_user WHERE user_id = ?");
					$stmt -> bind_param('i', $uid);
					$stmt -> execute();
					$stmt -> store_result();
					$stmt -> bind_result($fname, $lname);
					$stmt -> fetch();
				?>

 

Link to comment
Share on other sites

8 minutes ago, Adamhumbug said:

I just changed the closing TEXT; to have no white space on the same line and that has solved the issue.  Go Figure

I just pasted the code you posted into my editor. As you can see from the green text, everything following is considered part of the TEXT string

image.thumb.png.779412abdfbee5150f8d99778a69451e.png

It should look like this

image.thumb.png.14a12f6fcbb08471e75f2a98515e44b7.png

Link to comment
Share on other sites

5 hours ago, Adamhumbug said:

Hi Psyco,


echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='" . $uid . "' href='#userModal' data-firstname='" . $ufn ."' data-lastname='". $uln."' data-email='" . $ue . "' data-accountlevel='" . $ualid . "' data-mobile='".$um ."'data-role='".$urid."' data-active-sheets='".$ename."'>Manage</a></td>";

This is copied directly from my code

1738610673_Screenshot2019-12-18at09_17_19.png.07ffabb7a664d935758149c620d3b248.png

This is copied from inspector in Firefox for mac.

When i take the ' out of Chelsea's

i get the following


<a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="2" href="#userModal" data-firstname="Chelsea" data-lastname="Hockley" data-email="chelseahockley@hotmail.com" data-accountlevel="1" data-mobile="07783544882" data-role="1" data-active-sheets="A new event, Chelseas Event">Manage</a>

 

The PHP code you present puts single quotes around the parameter values, but you are stating that the code in the browser has double quotes around the parameter values. If that is the case, then something is changing the content. What you showed as the output for the $ename variable also points to this. I've known browsers to "self correct" bad code with respect to the display (e.g. if closing tags are missing they may assume they are there), but I've never known a browser to change the code (if that is what is doing it). That is going to make debugging much more difficult than it should be,

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.