php - 插入时 SQL 语法错误

Question

我遇到过类似这样的间歇性错误。

 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 '9 Sï¾')' at line 215

我并不是每次都明白这一点。这是它说发生错误的代码。我在存储在 session 中之前转义所有数据，然后将 session 插入数据库。第 215 行是

 '{$_SESSION['other-income-amount']}', 

这是完整的代码。

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO `application` (`ID`, `DATE`, `interested`, `intereseted2`,
`final`,
`type`,
`loan-type`,
`other-loan-type`,
`monthly-payment`,
`loan-total`,
`vehicle-submit`,
`name`,
`date-of-birth-month`,
`date-of-birth-day`,
`date-of-birth-year`,
`street`,
`city`,
`state`,
`zip`,
`marital-status`,
`personal-info-submit`,
`occupation`,
`salary`,
`date-of-employment`,
`employer-phone`,
`employer-phone2`,
`employer-phone3`,
`employer-address`,
`employer-city`,
`employer-state`,
`other-income`,
`other-income-amount`,
`income-submit`,
`mortgage`,
`creditor`,
`creditor2`,
`creditor3`,
`alimony`,
`orig-amount`,
`orig-amount1`,
`orig-amount2`,
`orig-amount3`,
`orig-amount4`,
`pres-balance`,
`pres-balance1`,
`pres-balance2`,
`pres-balance3`,
`mo-amount`,
`mo-amount1`,
`mo-amount2`,
`mo-amount3`,
`debts-info-submit`,
`reference-info-submit`,
`areacode`,
`middlethree`,
`lastfour`,
`email`,
`accountnumber`,
`month-of-employment`,
`day-of-employment`,
`year-of-employment`,
`relative-name`,
`relative-street`,
`relative-city`,
`relative-state`,
`relative-zip`,
`relative-phone1`,
`relative-phone2`,
`relative3`,
`relative-email`,
`relathionship`,
`posted_data`,
`personal-reason`,
`personal-submit`,
`total-amount`,
`other-submit`,
`additional-info`,
`joint-info-submit`,
`coname`,
`codate-of-birth-month`,
`codate-of-birth-day`,
`codate-of-birth-year`,
`costreet`,
`cocity`,
`costate`,
`cozip`,
`cophone`,
`cophone2`,
`cophone3`,
`coemail`,
`comarital-status`,
`coaccount`,
`cooccupation`,
`cosalary`,
`codate-of-employment-month`,
`codate-of-employment-day`,
`codate-of-employment-year`,
`coemployer-phone`,
`coemployer-phone-2`,
`coemployer-phone-3`,
`coemployer-address`,
`coemployer-city`,
`coemployer-state`,
`coother-income`,
`coother-income-amount`,
`no-hassle-amount`,
`employer-zip`,
`file`,
`ssn`
)
VALUES ('', '$date', 
'{$_SESSION['interested']}', 
'{$_SESSION['interested2']}', 
'{$_SESSION['final']}', 
'{$_SESSION['type']}', 
'{$_SESSION['loan-type']}', 
'{$_SESSION['other-loan-type']}', 
'{$_SESSION['monthly-payment']}', 
'{$_SESSION['loan-total']}', 
'{$_SESSION['vehicle-submit']}', 
'{$_SESSION['name']}', 
'{$_SESSION['date-of-birth-month']}', 
'{$_SESSION['date-of-birth-day']}', 
'{$_SESSION['date-of-birth-year']}', 
'{$_SESSION['street']}', 
'{$_SESSION['city']}', 
'{$_SESSION['state']}', 
'{$_SESSION['zip']}', 
'{$_SESSION['marital-status']}', 
'{$_SESSION['personal-info-submit']}', 
'{$_SESSION['occupation']}', 
'{$_SESSION['salary']}', 
'{$_SESSION['date-of-employment']}', 
'{$_SESSION['employer-phone']}', 
'{$_SESSION['employer-phone-2']}', 
'{$_SESSION['employer-phone-3']}', 
'{$_SESSION['employer-address']}', 
'{$_SESSION['employer-city']}', 
'{$_SESSION['employer-state']}', 
'{$_SESSION['other-income']}', 
'{$_SESSION['other-income-amount']}', 
'{$_SESSION['income-submit']}', 
'{$_SESSION['mortgage']}', 
'{$_SESSION['creditor']}', 
'{$_SESSION['creditor2']}', 
'{$_SESSION['creditor3']}', 
'{$_SESSION['alimony']}', 
'{$_SESSION['orig-amount']}', 
'{$_SESSION['orig-amount1']}', 
'{$_SESSION['orig-amount2']}', 
'{$_SESSION['orig-amount3']}', 
'{$_SESSION['orig-amount4']}', 
'{$_SESSION['pres-balance']}', 
'{$_SESSION['pres-balance1']}', 
'{$_SESSION['pres-balance2']}', 
'{$_SESSION['pres-balance3']}', 
'{$_SESSION['mo-amount']}', 
'{$_SESSION['mo-amount1']}', 
'{$_SESSION['mo-amount2']}', 
'{$_SESSION['mo-amount3']}', 
'{$_SESSION['debts-info-submit']}', 
'{$_SESSION['reference-info-submit']}', 
'{$_SESSION['areacode']}', 
'{$_SESSION['middlethree']}', 
'{$_SESSION['lastfour']}', 
'{$_SESSION['email']}', 
'{$_SESSION['accountnumber']}', 
'{$_SESSION['month-of-employment']}', 
'{$_SESSION['day-of-employment']}', 
'{$_SESSION['year-of-employment']}', 
'{$_SESSION['relative-name']}', 
'{$_SESSION['relative-street']}', 
'{$_SESSION['relative-city']}', 
'{$_SESSION['relative-state']}', 
'{$_SESSION['relative-zip']}', 
'{$_SESSION['relative-phone1']}', 
'{$_SESSION['relative-phone2']}', 
'{$_SESSION['relative3']}', 
'{$_SESSION['relative-email']}', 
'{$_SESSION['relathionship']}', 
'{$_SESSION['posted_data']}', 
'{$_SESSION['personal-reason']}', 
'{$_SESSION['personal-submit']}', 
'{$_SESSION['total-amount']}', 
'{$_SESSION['other-submit']}', 
'{$_SESSION['additional-info']}',
'{$_SESSION['joint-info-submit']}', 
'{$_SESSION['coname']}', 
'{$_SESSION['codate-of-birth-month']}', 
'{$_SESSION['codate-of-birth-day']}', 
'{$_SESSION['codate-of-birth-year']}', 
'{$_SESSION['costreet']}', 
'{$_SESSION['cocity']}', 
'{$_SESSION['costate']}', 
'{$_SESSION['cozip']}', 
'{$_SESSION['cophone']}', 
'{$_SESSION['cophone2']}', 
'{$_SESSION['cophone3']}', 
'{$_SESSION['coemail']}', 
'{$_SESSION['comarital-status']}', 
'{$_SESSION['coaccount']}', 
'{$_SESSION['cooccupation']}', 
'{$_SESSION['cosalary']}', 
'{$_SESSION['codate-of-employment-month']}', 
'{$_SESSION['codate-of-employment-day']}', 
'{$_SESSION['codate-of-employment-year']}', 
'{$_SESSION['coemployer-phone']}', 
'{$_SESSION['coemployer-phone-2']}', 
'{$_SESSION['coemployer-phone-3']}', 
'{$_SESSION['coemployer-address']}', 
'{$_SESSION['coemployer-city']}', 
'{$_SESSION['coemployer-state']}', 
'{$_SESSION['coother-income']}', 
'{$_SESSION['coother-income-amount']}',
'{$_SESSION['no-hassle-amount']}',
'{$_SESSION['employer-zip']}',
'{$_FILES["file"]["name"]}',
'{$encrypted}'
)";

我用这个转义然后存储到 session 中。

 foreach ($_POST as $key => $value) {
 $_POST[$key] = mysqli_real_escape_string($con, $value);
 }

Answer 1

输入您的值不带引号。而不是这个:

'{$_SESSION['other-income-amount']}', 

使用这个:

$_SESSION['interested'], $_SESSION['other-income-amount'], ...

您的代码将如下所示:

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO application (ID, DATE, interested, intereseted2,
final,
type,
loan-type,
other-loan-type,
monthly-payment,
loan-total,
vehicle-submit,
name,
date-of-birth-month,
date-of-birth-day,
date-of-birth-year,
street,
city,
state,
zip,
marital-status,
personal-info-submit,
occupation,
salary,
date-of-employment,
employer-phone,
employer-phone2,
employer-phone3,
employer-address,
employer-city,
employer-state,
other-income,
other-income-amount,
income-submit,
mortgage,
creditor,
creditor2,
creditor3,
alimony,
orig-amount,
orig-amount1,
orig-amount2,
orig-amount3,
orig-amount4,
pres-balance,
pres-balance1,
pres-balance2,
pres-balance3,
mo-amount,
mo-amount1,
mo-amount2,
mo-amount3,
debts-info-submit,
reference-info-submit,
areacode,
middlethree,
lastfour,
email,
accountnumber,
month-of-employment,
day-of-employment,
year-of-employment,
relative-name,
relative-street,
relative-city,
relative-state,
relative-zip,
relative-phone1,
relative-phone2,
relative3,
relative-email,
relathionship,
posted_data,
personal-reason,
personal-submit,
total-amount,
other-submit,
additional-info,
joint-info-submit,
coname,
codate-of-birth-month,
codate-of-birth-day,
codate-of-birth-year,
costreet,
cocity,
costate,
cozip,
cophone,
cophone2,
cophone3,
coemail,
comarital-status,
coaccount,
cooccupation,
cosalary,
codate-of-employment-month,
codate-of-employment-day,
codate-of-employment-year,
coemployer-phone,
coemployer-phone-2,
coemployer-phone-3,
coemployer-address,
coemployer-city,
coemployer-state,
coother-income,
coother-income-amount,
no-hassle-amount,
employer-zip,
file,
ssn
)
VALUES ('', $date, 
$_SESSION['interested'], 
$_SESSION['interested2'], 
$_SESSION['final'], 
$_SESSION['type'], 
$_SESSION['loan-type'], 
$_SESSION['other-loan-type'], 
$_SESSION['monthly-payment'], 
$_SESSION['loan-total'], 
$_SESSION['vehicle-submit'], 
$_SESSION['name'], 
$_SESSION['date-of-birth-month'], 
$_SESSION['date-of-birth-day'], 
$_SESSION['date-of-birth-year'], 
$_SESSION['street'], 
$_SESSION['city'], 
$_SESSION['state'], 
$_SESSION['zip'], 
$_SESSION['marital-status'], 
$_SESSION['personal-info-submit'], 
$_SESSION['occupation'], 
$_SESSION['salary'], 
$_SESSION['date-of-employment'], 
$_SESSION['employer-phone'], 
$_SESSION['employer-phone-2'], 
$_SESSION['employer-phone-3'], 
$_SESSION['employer-address'], 
$_SESSION['employer-city'], 
$_SESSION['employer-state'], 
$_SESSION['other-income'], 
$_SESSION['other-income-amount'], 
$_SESSION['income-submit'], 
$_SESSION['mortgage'], 
$_SESSION['creditor'], 
$_SESSION['creditor2'], 
$_SESSION['creditor3'], 
$_SESSION['alimony'], 
$_SESSION['orig-amount'], 
$_SESSION['orig-amount1'], 
$_SESSION['orig-amount2'], 
$_SESSION['orig-amount3'], 
$_SESSION['orig-amount4'], 
$_SESSION['pres-balance'], 
$_SESSION['pres-balance1'], 
$_SESSION['pres-balance2'], 
$_SESSION['pres-balance3'], 
$_SESSION['mo-amount'], 
$_SESSION['mo-amount1'], 
$_SESSION['mo-amount2'], 
$_SESSION['mo-amount3'], 
$_SESSION['debts-info-submit'], 
$_SESSION['reference-info-submit'], 
$_SESSION['areacode'], 
$_SESSION['middlethree'], 
$_SESSION['lastfour'], 
$_SESSION['email'], 
$_SESSION['accountnumber'], 
$_SESSION['month-of-employment'], 
$_SESSION['day-of-employment'], 
$_SESSION['year-of-employment'], 
$_SESSION['relative-name'], 
$_SESSION['relative-street'], 
$_SESSION['relative-city'], 
$_SESSION['relative-state'], 
$_SESSION['relative-zip'], 
$_SESSION['relative-phone1'], 
$_SESSION['relative-phone2'], 
$_SESSION['relative3'], 
$_SESSION['relative-email'], 
$_SESSION['relathionship'], 
$_SESSION['posted_data'], 
$_SESSION['personal-reason'], 
$_SESSION['personal-submit'], 
$_SESSION['total-amount'], 
$_SESSION['other-submit'], 
$_SESSION['additional-info'],
$_SESSION['joint-info-submit'], 
$_SESSION['coname'], 
$_SESSION['codate-of-birth-month'], 
$_SESSION['codate-of-birth-day'], 
$_SESSION['codate-of-birth-year'], 
$_SESSION['costreet'], 
$_SESSION['cocity'], 
$_SESSION['costate'], 
$_SESSION['cozip'], 
$_SESSION['cophone'], 
$_SESSION['cophone2'], 
$_SESSION['cophone3'], 
$_SESSION['coemail'], 
$_SESSION['comarital-status'], 
$_SESSION['coaccount'], 
$_SESSION['cooccupation'], 
$_SESSION['cosalary'], 
$_SESSION['codate-of-employment-month'], 
$_SESSION['codate-of-employment-day'], 
$_SESSION['codate-of-employment-year'], 
$_SESSION['coemployer-phone'], 
$_SESSION['coemployer-phone-2'], 
$_SESSION['coemployer-phone-3'], 
$_SESSION['coemployer-address'], 
$_SESSION['coemployer-city'], 
$_SESSION['coemployer-state'], 
$_SESSION['coother-income'], 
$_SESSION['coother-income-amount'],
$_SESSION['no-hassle-amount'],
$_SESSION['employer-zip'],
$_FILES['file']['name'],
$encrypted
)";

另外，研究 php 中的 sprintf 也会非常有用。谷歌一下!