AJAX / jQuery / PHP / MySQL - how to update a database

Question

I need to update a database using AJAX so don't have my page be reloaded. I can't find what's wrong and unexpectedly I get a success message back but a database doesn't get updated.

JS:

$('.start-time-class').submit(function() {                  

var startTime = "11:30";                
var projectID = 17;             
var userID = 2;             

$.ajax({
 url:'functions/starttime.php',
 data:{startTime:startTime,projectID:projectID,userID:userID},  // pass data 
 dataType:'json',
 success:function(){
  // something
 }                      
});         

});

PHP:

$con = mysqli_connect('localhost','smt','smt','smt');
if (!$con)
{
die('Could not connect: ' . mysqli_error($con));
}

$startTime = $_GET['startTime'];
$projectID = $_GET['projectID'];
$userID = $_GET['userID'];

mysqli_select_db($con,"ajax_demo");
$sql = "INSERT INTO 'uc_project_time'('userID', 'projectID', 'startTime') VALUES (". $userID .", ". $projectID .", ". $startTime .")";

$result = mysqli_query($con,$sql);      

mysqli_close($con);

Table: Remove the quotes. Columns: Remove the quotes. VALUES: Fix your quotes — Funk Forty Niner, Apr 02 '14 at 22:30
does it actually create a row without any values or its not inserting at all? — Abu Nooh, Apr 02 '14 at 22:30
Not even `$sql = "INSERT INTO uc_project_time (userID, projectID, startTime) VALUES ('$userID', '$projectID', '$startTime')";` ? That should work — Funk Forty Niner, Apr 02 '14 at 22:34
As an aside to my answer. `success:function(){ // something } ` is redundant and not required, unless you actually intend to do something upon successful input to the db. — Jack, Apr 02 '14 at 23:29

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Don't use quotes for table or column names

Use:

$sql = "INSERT INTO uc_project_time (userID, projectID, startTime) VALUES ('$userID', '$projectID', '$startTime')";

or

$sql = "INSERT INTO uc_project_time (userID, projectID, startTime) VALUES ('".$userID."', '".$projectID."', '".$startTime."')";

And do sanitize your code:

How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:50

Community

1
1

answered Apr 02 '14 at 22:34

Funk Forty Niner

74,450
15
68
141

@JackWilliams It's worth noting for sure Jack. – Funk Forty Niner Apr 02 '14 at 22:39
Maybe StackOverflow should start penalizing such users. – Jack Apr 02 '14 at 23:58
It actually does go into their track record. @JackWilliams Plus, flagging a question with a description also expedites the process ;-) Give them enough "rope"... – Funk Forty Niner Apr 03 '14 at 00:00

score 0 · Answer 2 · edited May 23 '17 at 11:57

You don't use quotes(single or double) in SQL for table or column names. You could use backticks(`), though not necessary(in your circumstance), it can be required in some situations.

When to use backticks(`)?

Firstly, you'd only use them in MySQL, as SQL Server and T-SQL use square brackets [] to denote identifiers.

If you were using spaces or keywords in your column or table names, you would need backticks. This would instruct the parser to parse the column or table name as a literal string.

To illustrate, if you had a table called 'badly named table'.

This wouldn't work

SELECT FROM badly named table...

This would work

SELECT FROM `badly named table`...

To conclude, backticks are useful if you have a bad table or column naming convention.

SQL Injections

Also, as @Fred -ii- said you're currently vulnerable to SQL Injections. If you're using PHP with PDO enabeled, you could use the following code (with prepared statements) protect against SQL injections and ensure that malicious actions can't be carried out on your database.

$con =  new PDO('mysql:host=localhost; dbname= name_of_db', 'name_of_user', 'password_of_user');
$sql = $con->
prepare("
    INSERT INTO uc_project_time userID, projectID, startTime VALUES (:userID,  :projectID, :startTime)
");
$sql->bindParam(':userID', $userID,':projectID', $projectID,':startTime', $startTime, PDO::PARAM_STR);
$sql->execute();
$rows = $sql->fetchAll(PDO::FETCH_ASSOC);

Or, If you want to continue using MySQLi, you could use their version of prepared statements. That said, I recommend PDO for the reasons illustrated here.

AJAX / jQuery / PHP / MySQL - how to update a database

2 Answers2

When to use backticks(`)?

SQL Injections