0

I am new to PHP and MySQL. My server has version 5.6 of MySQL. I am using procedural statements (not PDO, not OO). This is a PHP page on the site I am developing for users to create a new account. What's weird is that the error message is returned on the actual page itself (https://mywebsite.com/create_account.php) instead of in the error log on the server.

* HERE IS THE ERROR MESSAGE I RECEIVE: *

Error: INSERT INTO users (username, password, name_first, name_last, address_1, address_2, city, state, zip_code, email_address, phone_number, name_on_card, card_number_main, card_number_ccv, card_expire_mo, card_expire_yr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'

* HERE IS MY CODE THAT GENERATES THE ERROR MESSAGE: *

//
// Insert data into database
//

$sql = "INSERT INTO users (username, password, name_first, name_last, address_1, address_2, city, state, zip_code, email_address, phone_number, name_on_card, card_number_main, card_number_ccv, card_expire_mo, card_expire_yr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($link, $sql);
mysqli_stmt_bind_param($stmt, "ssssssssssssssss", $param_username, $param_password, $param_name_first, $param_name_last, $param_address_1, $param_address_2, $param_city, $param_state, $param_zip_code, $param_email_address, $param_phone_number, $param_name_on_card, $param_card_number_main, $param_card_number_ccv, $param_card_expire_mo, $param_card_expire_yr);
$param_username = $username;
$param_password = $password;
$param_name_first = $name_first;
$param_name_last = $name_last;
$param_address_1 = $address_1;
$param_address_2 = $address_2;
$param_city = $city;
$param_state = $state;
$param_zip_code = $zip_code;
$param_email_address = $email_address;
$param_phone_number = $phone_number;
$param_name_on_card = $name_on_card;
$param_card_number_main = $card_number_main;
$param_card_number_ccv = $card_number_ccv;
$param_card_expire_mo = $card_expire_mo;
$param_card_expire_yr = $card_expire_yr;
mysqli_stmt_execute($stmt);

I have read the manual as the error message suggests but to no avail. Any help would be most appreciated. Thank you.

aynber
  • 20,647
  • 8
  • 49
  • 57
Mike B
  • 11
  • 4
  • @Uueerdo They are parameters, not values. That should be valid. (That also is how the manual does it) – user3783243 Jul 11 '18 at 20:22
  • 2
    Where are you doing the error reporting? Are you sure you aren't passing `$sql` to a `query(` call? The error is being thrown at the first placeholder so I'd think this must be going into something that doesn't know how to handle bindings. – user3783243 Jul 11 '18 at 20:25
  • @user3783243 He isn't. He's passing `$sql` to a prepare call which is then executed later on. This is how you do binding. – Jonathan Jul 11 '18 at 20:26
  • 1
    **WARNING**: Writing your own access control layer is not easy and there are many opportunities to get it severely wrong. Please, do not write your own authentication system when any modern [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) comes with a robust [authentication system](https://laravel.com/docs/master/authentication) built-in. At the absolute least follow [recommended security best practices](http://www.phptherightway.com/#security) and **never store passwords as plain-text** or a weak hash like **SHA1 or MD5**. – tadman Jul 11 '18 at 20:27
  • 1
    Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Jul 11 '18 at 20:27
  • 4
    There's no reason to create variables that are merely copies of other variables. Just bind to the variables that contain the correct values. – tadman Jul 11 '18 at 20:27
  • 2
    @Jonathan Please read my whole comment, I'm aware how bindings work. If he is doing `if(...query($sql)` though to test it would produce this error. Do you have a theory for why `prepare` would ever complain about a `?`?? – user3783243 Jul 11 '18 at 20:27
  • 1
    It does appear that the placeholder query is being run somewhere else, perhaps unintentionally. As redundant as a lot of this code is, this should work. – tadman Jul 11 '18 at 20:29
  • @user3783243 good point. I missed a word reading your comment and interpreted it different. I also don't see anything wrong with this query/code other than it appears as though they are storing credit card information which is IMO even worse than using unhashed passwords. – Jonathan Jul 11 '18 at 20:31

0 Answers0