Click here to Skip to main content
15,796,299 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'keys SET keyNum = '36332', employeeID = '4 ', dateKeyGiven = '2022-11-30', da...' at line 1 in C:\xampp\htdocs\acceso-stephanie\confirmedit_key.php:19 Stack trace: #0 C:\xampp\htdocs\acceso-stephanie\confirmedit_key.php(19): mysqli_query(Object(mysqli), 'UPDATE keys SET...') #1 {main} thrown in C:\xampp\htdocs\acceso-stephanie\confirmedit_key.php on line 19


My code:

<?php

	include_once 'database/dbConnect.php';
	session_start();
	if(!isset($_SESSION['admin']))
	{
		header ("Location: loginpage.php");
	}

    $_SESSION['editkey']['id'] = $_POST['id'];
	$_SESSION['editkey']['keyNum'] = $_POST['keyNum'];
	$_SESSION['editkey']['employee'] = $_POST['employee'];
	$_SESSION['editkey']['dateKeyGiven'] = $_POST['dateKeyGiven'];
	$_SESSION['editkey']['dateKeyReturned'] = $_POST['dateKeyReturned'];
	$_SESSION['editkey']['user'] = $_POST['user'];

	$sql = "UPDATE keys SET keyNum = '".$_SESSION['editkey']['keyNum']."', employeeID = '".$_SESSION['editkey']['employee']."', dateKeyGiven = '".$_SESSION['editkey']['dateKeyGiven']."', dateKeyReturned = '".$_SESSION['editkey']['dateKeyReturned']."', userID = '".$_SESSION['editkey']['user']."' WHERE id = ".$_SESSION['editkey']['id'];

	$editkey_query = mysqli_query($mysqli, $sql);

	if (count($_POST) > 0)
	{
		$sql = "INSERT INTO `log`(`user`, `date`, `transtype`, `purpose`) VALUES ('".$_SESSION['admin']."', NOW(), 'Updated key ".$_SESSION['editkey']['keyNum']."', 'Edit key information')";

		if (mysqli_query($mysqli, $sql))
		{}
		else
		{
			echo '<script>alert("Error record: " . mysqli_error($mysqli)!");</script>';
		}
			mysqli_close($mysqli);
	}

	echo ("<SCRIPT LANGUAGE='JavaScript'>
    window.alert('Succesfully Updated')
    window.location.href='keys.php';
    </SCRIPT>");
?>


Receiving a form submit from:

<Style>
	.button {
	border: none;
	color: white;
	padding: 16px 32px;
	text-align: center;
	text-decoration: none;
	display: inline-block;
	font-size: 16px;
	margin: 4px 2px;
	transition-duration: 0.4s;
	cursor: pointer;
	}

	.button-back {
	background-color: white; 
	color: black; 
	border: 2px solid #4CAF50;
	}

	.button-back:hover {
	background-color: #4CAF50;
	color: white;
	}
</style>


<?php
	require_once ('database/dbConnect.php');
	session_start();

	if(!isset($_SESSION['admin']))
	{
		header ("Location:loginpage.php");
	}

	$sql = ("SELECT * from `keys` WHERE keys.id = " .$_GET['id']);
	$qry = mysqli_query($mysqli, $sql);
	$edit = mysqli_fetch_assoc($qry);

	$emp_sql = "SELECT * FROM `employee`";
	$emp_query = mysqli_query($mysqli, $emp_sql);

	$user_sql = "SELECT * FROM `users`";
	$user_query = mysqli_query($mysqli, $user_sql);

				$employeeID = $edit['employeeID'];

				$eid = ("SELECT firstName, lastNames
						FROM employee
						WHERE employee.id = '$employeeID'");

				$eidRes = mysqli_query($mysqli, $eid);
				$employee = mysqli_fetch_array($eidRes);

				$userID = $edit['userID'];

				$uid = ("SELECT firstName, lastNames
						FROM users
						WHERE users.id = '$userID'");

				$uidRes = mysqli_query($mysqli, $uid);
				$user = mysqli_fetch_array($uidRes);
?>



<html>

<head>
	<title>Edit key</title>
    <link href = "CSS/style_for_edit.css" rel = "stylesheet" media ="all">

</head>

<?php
	include("mainheader.php");
		if(!isset ($_GET['page']))
	{}
?>

<body>
	
	<div class="divider"></div>

	<a href = "keys.php"><button class = "button button-back" style = "float: left;">Back</button></a>

<div class = "emp-edit">
	<div class = "emp-body">

	<center><h1>Edit key</h1></center>
	<center><h3>REMINDER: Only authorized users can edit keys.</h3></center>

	<form method = "post" action = "confirmedit_key.php" enctype = "multipart/form-data">

	<input type = "hidden" name = "id" value = "<?php echo $edit['id'];?>" required = "required">

				<div class="editor editor-space">
					<div class="col">
						<div class="input-group">
							<label>Key number:</label>
							<input class="input--style-1" type="text" name="keyNum" value = "<?php echo $edit['keyNum'];?>">
						</div>
					</div>
					<div class="col">
						<div class="input-group">
							<label>Employee:</label>
							<input class="input--style-1" type="text" name="emp" value="<?php echo $employee['firstName'] . " " . $employee['lastNames'];?>">
							<br/><br/>
							<label>Change selected employee:</label>
							<select name="employee">
								<?php

								while ($employee = mysqli_fetch_array ($emp_query, MYSQLI_ASSOC)):;?>
									<?php echo $employee["employeeID"];?>
									<option value="<?php echo $employee["id"];?> ">
										<?php echo $employee["firstName"] . " " . $employee["lastNames"]; ?>
									</option>
								
								<?php
									endwhile;
								?>
							</select>
						</div>
					</div>
				</div>

				<div class="editor editor-space">
					<div class="col">
						<div class="input-group">
							<label>Date key given:</label>
							<input class="input--style-1" type="date" name="dateKeyGiven" value = "<?php echo $edit['dateKeyGiven'];?>">
						</div>
					</div>
					<div class="col">
						<div class="input-group">
							<label>Date key returned:</label>
							<input class="input--style-1" type="date" name="dateKeyReturned" value = "<?php echo $edit['dateKeyReturned'];?>">
						</div>
					</div>
				</div>

				<div class="editor editor-space">
					<div class="col">
							<div class="input-group">
								<label>User:</label>
								<input class="input--style-1" type="text" name = "u" value="<?php echo $user['firstName'] . " " . $user['lastNames'];?>">
								<br/><br/>
								<label>Change selected employee:</label>
								<select name="user">
									<?php

									while ($user = mysqli_fetch_array ($user_query, MYSQLI_ASSOC)):;?>

										<option value="<?php echo $user["id"];?> ">
											<?php echo $user["firstName"] . " " . $user["lastNames"]; ?>
										</option>
									
									<?php
										endwhile;
									?>
								</select>
							</div>
					</div>
				</div>

                <br><br>
                	<div class = "p-t-20">
                        <input type = "submit" name = "submit" value = "Upload">
					</div>
                        
    </form>
</div>
</div>
</div>
</div>

</body>
</html>


What I have tried:

I've tried a lot of tutorials and help online, but I just can't figure out what's wrong.
Posted
Updated 30-Nov-22 12:09pm

1 solution

Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that throughout your whole app and the chances are the problem you have noticed will go away at the same it.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900