Python mysql statement returning error when called from php

Question

I have a python script which takes csv as an input and insert its data to Database. and I am calling this script from a php script where I am generating this CSV. If I call python script from terminal and pass csv to it, it works perfectly but if I call it from php using

exec('python bulk_metadata_ingest_dev.py '. $metadataCSV);

It throws a mysql error while inserting to a table.

_mysql_exceptions.ProgrammingError: (1064, '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 \'text to insert""\' at line 1')

Query which throwing this error :

cursor = db.query("insert into test_table(asset_type, attribute_id, asset_title, is_publishable, owner) values(\""+str(mediaAssetTypeDict[sample_content.media_type.lower()])+"\", \""+str(attributeId)+"\", \""+str(assetTitle)+"\", \""+str(publishAssets)+"\", \""+str(sample_content.content_owner)+"\" )")

In python I don't know how to handle this problem. Thanks in advance

Interesting question by @antonio_antuan and what if you reduce the need to escape by using instead cursor = db.query("insert into test_table(asset_type, attribute_id, asset_title, is_publishable, owner) values('"+str(mediaAssetTypeDict[sample_content.media_type.lower()])+"', '"+str(attributeId)+"', '"+str(assetTitle)+"', '"+str(publishAssets)+"', '"+str(sample_content.content_owner)+"')") ? I know databases, that do not like double quotes around string literals ... — Dilettant
– Dilettant, Commented Jul 10, 2016 at 19:55
@Dilettant Thanks man It worked. But its still a mystery for me why this python script works fine when I run it directly from terminal with the same input file. — Tushar Gaurav
– Tushar Gaurav, Commented Jul 11, 2016 at 5:16
If I had "such a task" of write from a python script into a database, I would next simplify and secure the proxy a bit, i.e. try a prepared statement, not construct the string to "query" inside the parentheses of the call operator, but maybe build a tuple and then fill in the "blanks" of the prepared statement. This reduces the many layered single quote, double quote string call onion skins, is more explicit, does not smell like presentation state manipulations and is more safe, when accepting input from outside a boundary of trust. But good it now let's you continue. Thanks for the feedback. — Dilettant
– Dilettant, Commented Jul 11, 2016 at 5:54

Andrew · Accepted Answer · 2016-07-10 20:08:16Z

1

It looks like you are using a mySQL reserved word OWNER as a column name in your table and not using backticks. Try changing your select to:

...("insert into test_table(asset_type, attribute_id, asset_title, is_publishable, `owner`")... 
                                                                                   ^     ^

Check out Table 9.2 in the docs for a complete list.

answered Jul 10, 2016 at 20:08

Andrew

425 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python mysql statement returning error when called from php

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related