Jump to content
phreak3r

Issue with inserting data into database #2

Recommended Posts

I am having another issue with inserting data into the database again. I am a bit tired at the moment, so pardon any mistakes/errors. I usually don't like to go to bed with problems unsolved.

So, upon registration, the user is taken to a page with a message informing them that their data was sent to the server, yadda, yadda confirmation of account is need.

However, when refreshing the database or the table, the newly registered user is not showing up in the database. Here's the particular piece of code that controls this (it appears to be fine to me):

	$username = mysqli_real_escape_string($conn, $_POST['username']);
	$password = mysqli_real_escape_string($conn, $_POST['password']);
	$hashed_password = mysqli_real_escape_string($conn, password_hash($password, PASSWORD_DEFAULT));
	$email = mysqli_real_escape_string($conn, $_POST['email_address']);
	$confirmation_status = 0;
	$account_open_date = date('d-m-Y H:i:m');
	$account_age = $account_open_date;

	$sqlinsert = "INSERT INTO profile0 (username, password, email_address, confirmation_status, account_open_date, account_age) VALUES ('$username', '$hashed_password', '$email', '$confirmation_status', '$account_open_date', '$account_age')";
	$result = mysqli_query($conn, $sqlinsert);

I must also note that prior to including variables account_open_date and account_age into the SQL statement, there were no errors and data was being inserted into the database with no problem. There are no errors in the error log on my end, I am pretty clueless.

Share this post


Link to post
Share on other sites

Use mysqli_affected_rows() to determine if the query inserted anything, and if not log the $sqlinsert somewhere so you can try running it manually.

Share this post


Link to post
Share on other sites

I don't see any error checking in that code. Have you used mysqli_report before connecting to the db server?

 

eg

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Share this post


Link to post
Share on other sites

Use mysqli_affected_rows() to determine if the query inserted anything, and if not log the $sqlinsert somewhere so you can try running it manually.

 

I think the query did not insert anything at all, if so I would have seen it in the database. Here is what the mysql log looks like (the apache log is error-free):

2018-01-17T23:07:04.089990Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-01-17T23:07:04.090083Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2018-01-17T23:07:04.274857Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-01-17T23:07:04.456628Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-0ubuntu0.17.10.1) starting as process 905 ...
2018-01-17T23:07:04.905887Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-01-17T23:07:04.905938Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-17T23:07:04.905944Z 0 [Note] InnoDB: Uses event mutexes
2018-01-17T23:07:04.905947Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-01-17T23:07:04.905950Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-01-17T23:07:04.905953Z 0 [Note] InnoDB: Using Linux native AIO
2018-01-17T23:07:04.925266Z 0 [Note] InnoDB: Number of pools: 1
2018-01-17T23:07:04.935852Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-01-17T23:07:04.936710Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-01-17T23:07:04.946154Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-01-17T23:07:04.954066Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-01-17T23:07:05.032222Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-17T23:07:05.339294Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-01-17T23:07:05.339343Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-01-17T23:07:05.578341Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-01-17T23:07:05.578839Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-01-17T23:07:05.578847Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-01-17T23:07:05.579286Z 0 [Note] InnoDB: Waiting for purge to start
2018-01-17T23:07:05.629441Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 2879136
2018-01-17T23:07:05.629751Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-01-17T23:07:05.656916Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-01-17T23:07:06.308865Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-01-17T23:07:06.308902Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-01-17T23:07:06.308917Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-01-17T23:07:06.308952Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-01-17T23:07:06.372336Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180117 17:07:06
2018-01-17T23:07:06.912378Z 0 [Note] Event Scheduler: Loaded 0 events
2018-01-17T23:07:06.912799Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.20-0ubuntu0.17.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-01-17T23:07:06.912828Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2018-01-17T23:07:06.912845Z 0 [Note] Beginning of list of non-natively partitioned tables
2018-01-17T23:07:07.431913Z 0 [Note] End of list of non-natively partitioned tables
2018-01-17T23:07:07.626505Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

P.S. I will try restarting the server.

Share this post


Link to post
Share on other sites

I don't see any error checking in that code. Have you used mysqli_report before connecting to the db server?

 

eg

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

 

No I have/am not.

Edited by phreak3r

Share this post


Link to post
Share on other sites

Restarting the MySQL server did not help. I think the problem may be that I have one of the types set as DATETIME.

Share this post


Link to post
Share on other sites

Don't guess, check for mysql errors.

 

The easiest way is to use mysqli_report() (as suggested above). The alternative is to test for errors after very query, which can mean a lot of extra code.

 

[edit] PS: if you are storing a datetime value then it should be DATETIME type

Edited by Barand

Share this post


Link to post
Share on other sites

Don't guess, check for mysql errors.

 

The easiest way is to use mysqli_report() (as suggested above). The alternative is to test for errors after very query, which can mean a lot of extra code.

 

[edit] PS: if you are storing a datetime value then it should be DATETIME type

 

Like this, yeah?

<?php
$servername   = "localhost";
$database = "soapbox";
$username = "root";
$password = "1234";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
mysqli_select_db($conn, $database);

/*if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
} else {
	echo "Connection successful!";
}

if (!mysqli_select_db($conn, $database)) {
	echo " Database not selected!";
} else {
	echo " Database selected!";
}*/
?>

EDIT: Not sure what I am supposed to be looking out for in the console, with this added.

Edited by phreak3r

Share this post


Link to post
Share on other sites

the reporting and display of any exceptions due to the mysqli_report(...) statement are dependent on php's error_reporting and display_errors settings.

 

do you have (and have confirmed using a phpinfo() statement) php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system?

Share this post


Link to post
Share on other sites

the reporting and display of any exceptions due to the mysqli_report(...) statement are dependent on php's error_reporting and display_errors settings.

 

do you have (and have confirmed using a phpinfo() statement) php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system?

 

Uh.....no I do not think I do. Let me go and find that.

Share this post


Link to post
Share on other sites

Yes

 

Okay, thank you, I will go ahead and follow the instructions of the poster before your previous response.

Share this post


Link to post
Share on other sites

Bingo! All errors have been turned on:

[Thu Jan 18 00:51:50.905149 2018] [php7:error] [pid 13738] [client 127.0.0.1:39012] PHP Fatal error:  Uncaught mysqli_sql_exception: Incorrect datetime value: '18-01-2018 00:51:01' for column 'account_open_date' at row 1 in /var/www/html/soapbox/confirmation.php:17\nStack trace:\n#0 /var/www/html/soapbox/confirmation.php(17): mysqli_query(Object(mysqli), 'INSERT INTO pro...')\n#1 {main}\n  thrown in /var/www/html/soapbox/confirmation.php on line 17, referer: http://localhost/soapbox/signup.php

If that is not a suitable datetime value, then what is?

Edited by phreak3r
  • Like 1

Share this post


Link to post
Share on other sites

YYYY-MM-DD HH:MM:SS format is.

 

See also: Mysql Date and time literals

 

Oh, I guess this will not work with MySQL then:

$account_open_date = date('d-m-Y H:i:m');

Thank You!

 

EDIT: I am still having issues, unfortunately.

Edited by phreak3r

Share this post


Link to post
Share on other sites

Just specify as DATETIME without any size constraint (a datetime can hold the range 1901-01-01 00:00:00 to 9999-12-31 23:59:59. RTFM Kicken gave you the link)

Share this post


Link to post
Share on other sites

Just specify as DATETIME without any size constraint (a datetime can hold the range 1901-01-01 00:00:00 to 9999-12-31 23:59:59. RTFM Kicken gave you the link)

 

I have removed the size constraint, the error still persists.

PHP Fatal error:  Uncaught mysqli_sql_exception: Incorrect datetime value: '2018-Jan-Thu' for column 'account_open_date' at row 1 in /var/www/html/soapbox/confirmation.php:17\nStack trace:\n#0 /var/www/html/soapbox/confirmation.php(17): mysqli_query(Object(mysqli), 'INSERT INTO pro...')\n#1 {main}\n  thrown in /var/www/html/soapbox/confirmation.php on line 17, referer: http://localhost/soapbox/signup.php

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.