Fatal error: uncaught mysqli_SQL_exception: you have an error in your SQL syntax;

Question

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 30-Nov-22 10:03am

Suga Koru

Updated 30-Nov-22 11:09am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2022-11-30T11:09:00

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.